Help with the query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with the query

Author

Topic

tapas16880
Starting Member

2 Posts

Posted - 2012-06-28 : 04:02:29

I have got a table like below:

Order No Name Amt
100 ABC 50
100 DEF 40
200 ABC 80
300 FRC 44
300 SDE 90
400 ABC 200
500 EWQ 40
500 QWE 50

I want to retrieve the records where the NAME is "ONLY" ABC i.e. there will be one record for that order no where the name is ABC

So, the result should give me:

Order No Name Amt
200 ABC 80
400 ABC 200

Can you please tell me how to write an efficient query to get this result?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-06-28 : 04:47:28

SELECT [Order No], Name, Amt
FROM (
SELECT *, COUNT(*) OVER (PARTITION BY [Order No]) AS cnt FROM dbo.Table1
) AS d
WHERE cnt = 1 AND Name = 'ABC'

N 56°04'39.26"
E 12°55'05.63"

tapas16880
Starting Member

2 Posts

Posted - 2012-06-28 : 05:52:27

Thanks but it doesn't work as I use SAS Enterprise Guide and use PROC SQL to run the queries.

It didn't recognise OVER (PARTITION BY) clause. I tried removing this but still didn't work(the query finished but no data).

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-06-29 : 08:37:27

This is a Microsoft SQL Server forum.
Try your luck over at www.dbforums.com

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources