Prev: Qs re CTE
Next: Interested in SQL Server Audit Tool
From: SAL on 20 Apr 2010 12:31
Hello,
I need some help with a query that I can't quite figure out the logic for.
I need to select the primary key field from table1 where there are no
records in which field1 are null and in which the number of records in which
field1 =1 is greater than zero.

I've tried several permutations to get this query to work and have not had
success. Can someone give me a hand please?

Thanks
S


From: Plamen Ratchev on 20 Apr 2010 12:48
I am not sure I understand the requirements, but see if the following example will help:

CREATE TABLE table1 (
keycol INT NOT NULL PRIMARY KEY,
datacol INT);

INSERT INTO table1 VALUES(1, NULL);
INSERT INTO table1 VALUES(2, 2);
INSERT INTO table1 VALUES(3, 1);
INSERT INTO table1 VALUES(4, 1);
INSERT INTO table1 VALUES(5, 3);
INSERT INTO table1 VALUES(6, 1);

SELECT keycol, datacol
FROM (
SELECT keycol, datacol,
SUM(CASE WHEN datacol = 1 THEN 1 END) OVER(PARTITION BY datacol) AS cnt
FROM table1
WHERE datacol IS NOT NULL) AS T
WHERE cnt > 1;

/*

keycol datacol
----------- -----------
3 1
4 1
6 1

*/

--
Plamen Ratchev
http://www.SQLStudio.com
From: --CELKO-- on 20 Apr 2010 13:04
>> I need to select the primary key field [sic: columns are not fields] from Table1 where there are no records [sic: rows are not records] in which field1 are [sic: is] NULL and in which the number of records [sic] in which field1 =1 is greater than zero. <<

PRIMARY KEYs are unique in each row. Grouping by a key will give you
one and only one row by definition. So the column named "field1" can
have one and only value or be NULL.

Please post DDL and some sample data.
From: SAL on 20 Apr 2010 13:23
Thank you for your replies. Below is the DDL and a few rows in the table.
I need the view to return to me AnnId = 295 and 297

So, for a particular AnnId, give me the rows in which there are no null
values for transfersAtAnn and where there is at least one transfersAtAnn = 1

Does this make sense?

CREATE TABLE [dbo].[AffectedEasementsGASB](
[AEGID] [int] IDENTITY(1,1) NOT NULL,
[AnnId] [int] NULL,
[CtyId] [int] NULL,
[Sqft] [int] NULL,
[TransfersAtAnn] [bit] NULL,
CONSTRAINT [PK_AffectedEasementsGASB] PRIMARY KEY CLUSTERED
(
[AEGID] ASC
) ON [PRIMARY]
)

AEGID AnnId ctyId sqft transfersAtAnn
4 99 2029 99999 null
24 123 2028 868074045 null
25 93 2028 866252791 null
35 295 2053 155864 1
36 296 2054 10001 null
37 296 2054 11002 1
38 297 2055 9999 0
39 297 2055 9898 1


"--CELKO--" <jcelko212(a)earthlink.net> wrote in message
news:c549cf0b-1a5c-4ee7-bb44-1068152d17eb(a)q31g2000prf.googlegroups.com...
>> I need to select the primary key field [sic: columns are not fields] from
>> Table1 where there are no records [sic: rows are not records] in which
>> field1 are [sic: is] NULL and in which the number of records [sic] in
>> which field1 =1 is greater than zero. <<

PRIMARY KEYs are unique in each row. Grouping by a key will give you
one and only one row by definition. So the column named "field1" can
have one and only value or be NULL.

Please post DDL and some sample data.


From: --CELKO-- on 20 Apr 2010 14:20
>> Does this make sense? <<

Better, but the design is still awful. IDENTITY is not relational and
cannot be a key; do you have a real key? or is this a sequential tape
file written with SQL? My first guess was that since you don't know
RDBMS, you would think that a key has to be one column, and did not
make a proper key. But the only possible key is the first three
columns! That makes no sense to me.

You have more NULL-able columns than major corporation databases; you
don't know ISO-11179 rules for names. SQL programmers do not use BIT
flags -- that was mag tapes and asembly language programming. Even
worse, in SQL Server BIT is an extact numeric that can be {0, 1,
NULL}.

The data element "TransfersAtAnn" is a verb phrase and not a noun. How
can an attribute be a verb? Did you mean something like
"ann_transfer_flg BIT DEFAULT 0 NOT NULL" ?

This seems to have to do with real estate. Can I guess that square
footage is never less than zero? But why would it keep changing? If
this is a hisgory, where is the temporal data?

Here is my best guess:

CREATE TABLE AffectedEasements
(ann_id INTEGER NOT NULL, -- who or what is ann?
cty_id INTEGER NOT NULL, -- city identifier?
something_squarefootage INTEGER NOT NULL
CHECK (something_squarefootage > 0),
ann_transfer_flg BIT, -- 3-valued bit! non-relational!
-- only possible key!
PRIMARY KEY (ann_id, city_id, something_squarefootage));

>>So, for a particular ann_id, give me the rows in which there are no NULL values for ann_transfer_flg and where there is at least one ann_transfer_flg = 1 <<

SELECT DISTINCT ann_id, cty_id
FROM AffectedEasements
WHERE ann_id = @in_ann_id
GROUP BY ann_id, cty_id
HAVING MIN(IFNULL(ann_transfer_flg, -1)) IN (0,1)
AND MAX(IFNULL(ann_transfer_flg, -1))= 1;

You really need to start over with this schema. It is a textbook bad
example.
 |  Next  |  Last
Pages: 1 2 3
Prev: Qs re CTE
Next: Interested in SQL Server Audit Tool