distinct in query problem - 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

distinct in query problem

Author

Topic

Richard Steele
Starting Member

11 Posts

Posted - 2008-06-24 : 14:24:53

I have a problem in pulling distinct item numbers from a query.

SELECT TOP (50) SUBSTRING(s.Number, 1, 5) AS number, s.Shelf_StockIncreased, s.AutoID from stock s

Using distinct in the above query doesn't help as there are duplicates in the number above and there are unique autoids.

How do I then show only the records that have a distinct number?

Many thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-24 : 14:27:02

Can you post some sample data with expected result?

Madhivanan

Failing to plan is Planning to fail

Richard Steele
Starting Member

11 Posts

Posted - 2008-06-24 : 14:53:42

Sample data from query:

'ID728 HB-2', 6/24/2008 12:05:00 PM, 2467
'ID728 SB-1', 6/24/2008 1:05:00 PM, 2468
'AP100 HB-2', 6/24/2008 12:15:00 PM, 2469

Only want to show distinct item numbers ID728 and AP100 and not both ID728s. Thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-24 : 15:05:14

Since AutoId is unique if you include it in the SELECT statement it wont give the correct results
Do you still need any AutoId?

SELECT TOP (50) SUBSTRING(s.Number, 1, 5) AS number, max(s.Shelf_StockIncreased) as Shelf_StockIncreased, max(s.AutoID) as AutoId from stock s group by SUBSTRING(s.Number, 1, 5)

Madhivanan

Failing to plan is Planning to fail

Richard Steele
Starting Member

11 Posts

Posted - 2008-06-24 : 15:13:06

That'll work! Thanks!

Richard Steele
Starting Member

11 Posts

Posted - 2008-06-24 : 15:57:10

Well, I've made this a bit more complex (as needed) and I can't get it to return the distinct item numbers (number). What am I doing wrong? Thanks in advance.

SELECT DISTINCT
TOP (50) MAX(SUBSTRING(s.Number, 1, 5)) AS number, MAX(s.Shelf_StockIncreased) AS Shelf_StockIncreased, MAX(s.AutoID) AS AutoID,
MAX(i.HARD_ISBN) AS Hard_ISBN, MAX(i.SOFT_ISBN) AS Soft_ISBN, i.TITLE2X, i.AUTHORSX
FROM Stock AS s INNER JOIN
Inventory AS i ON i.CATALOG = SUBSTRING(s.Number, 1, 5)
WHERE (s.Units > 0)
GROUP BY s.Number, i.TITLE2X, i.AUTHORSX, s.Shelf_StockIncreased
ORDER BY s.Number DESC

Richard Steele
Starting Member

11 Posts

Posted - 2008-06-24 : 16:14:57

Ok, I've taken the autoid out and it now works. However, I don't understand why that would make a difference since it was in there a max(autoid). Please explain. Thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-24 : 19:07:14

quote:
Originally posted by Richard Steele

Ok, I've taken the autoid out and it now works. However, I don't understand why that would make a difference since it was in there a max(autoid). Please explain. Thanks.

I think it is because you use s.Shelf_StockIncreased column in both on aggregate function and Group by clause

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-25 : 00:59:20

quote:
Originally posted by Richard Steele

Ok, I've taken the autoid out and it now works. However, I don't understand why that would make a difference since it was in there a max(autoid). Please explain. Thanks.

Try removing s.Shelf_StockIncreased from GROUPBY. Also it would be better if you can post some data along with i.TITLE2X, i.AUTHORSX to give us clear idea of how values are in those fields

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources