Select 1 of many records 5

[markajem]

T-Sql

My code is pulling all records where field2 = 0. In tableA are sales orders. Each sales order may have multiple records. Those multiple records will contain either a 0 or 1 in field2. What I need is to only pull from the many records for a sales order (field1) only ONE record where field2 = 0 even if there are more than one record for field1 where field2 = 0. Does not matter which record, I just need to know that there is at least 1 record for the same sales order where field2 = 0.

Thanks

Select field1, field2

from tableA

where field2 = 0

Present records:
[tt]
field1 field2
778899 0
778899 0
778899 1
882222 1
882222 0
998888 0
[/tt]

Desired results:
[tt]
field1 field2
778899 0
882222 0
998888 0
[/tt]

 

[Zathras]

Select [COLOR=red][b]DISTINCT[/b][/color] field1, field2

from tableA

where field2 = 0

 

[SQLDenis]

Select distinct field1, field2

from tableA

where field2 = 0

or

Select field1, field2

from tableA

where field2 = 0
group by field1, field2

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

How about...

Select [!]Distinct[/!] field1, field2

from tableA

where field2 = 0

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[hwkranger]

select top 1...

SELECT TOP 1 field1, field2
FROM tableA
WHERE field2 = 0 AND field1 = [some number]

or you can use aggregation and having

SELECT field1,
FROM table1
GROUP BY field1, field2
HAVING field2 = 0

I always forget if having or group by comes first... so if it's backwards my apologies.

Randall Vollen
National City Bank Corp.

 

[SQLDenis]

I always forget if having or group by comes first... so if it's backwards my apologies.

Here is a trick, use alphabetical order
G comes before H
Group.....
Having.....

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[hwkranger]

SQLDenis,

That's an AWESOME memory device! I ussually just wing it, and if it's wrong I swap it...

Star to you!

Randall Vollen
National City Bank Corp.

 

[markajem]

Used below code and worked great

SELECT field1,
FROM table1
GROUP BY field1, field2
HAVING field2 = 0

Thanks to everyone that helped me.

 

[Zathras]

markajem, the final result is the same, but that way the database has to read and summarize ALL of the records in the table even though you only want the ones WHERE field2 equals zero. Not very efficient.

If you have an index over field2, then by selecting ONLY the zero records with a WHERE clause, the database only has to read those records where field2 is zero. Better yet, if the index is built over field2,field1 (in that order), the database doesn't have to look at ANY records in table1. It can satisfy the simple query (using DISTINCT instead of GROUP BY) by looking only at the index. It doesn't get much more efficient than that.

Even without an index, it would be more efficient to summarize only those rows that are selected instead of summarizing all of them.

--------------------------------------------------

As for HAVING vs. WHERE: Instead of some artificial mnemonic device that could fail you (was that alphabetical order or inverse alphabetical order?) you can simply remember that you almost always use a WHERE clause following the SELECT, and when you use a GROUP BY you have the ability to make selection at two points: (1) Before grouping based on raw column data (WHERE), and (2) After grouping based on computed results (HAVING).

 

[markajem]

Zathras thanks for the extra tips. Will study up on them.
Meantime, this should be ok as is. The data tables are not that large at all. Few hundred records. I have combined the VIEW with a view more and use them in a crystal report. The report pops up almost instantly with data.