Select Statement Question - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Select Statement Question

Author

Topic

aunabidi
Starting Member

11 Posts

Posted - 2009-03-26 : 21:26:31

Hello,

I have a question that I hope someone has an answer to :D

I have a table with 4 fields: date, points, ID, and amount. This table is populated with entries that I want to sort by date.

Lets say we have these entries:


Date        Points    ID         Amount
02/02/09     4         1           10          
02/02/09     3         2           15
02/02/09     4         3           12
02/02/09     2         4           16
02/03/09     2         1           20
02/03/09     3         2           14

The ID is unique only on the same day. I need a select statement that will get me an entry with the highest points for each day. And if there are 2 or more entries(for example date 02/02/09) with the highest points for each day it should display the entry with the greatest ID number.

So the final display for this table should be:


Date        Points    ID         Amount
02/02/09     4         3           12
02/03/09     3         2           14

I have thought a lot about this and I can't come to a solution. Any help would be great. If you have any questions please ask.

Thank you!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-26 : 21:40:03

Maybe this?

SELECT D.Date,D.Points,D.ID,D.Amount FROM
(
SELECT Date,Points,ID,Amount,Row_Number() OVER (PARTITION BY Date ORDER BY Points,ID DESC) as Seq FROM <urtable>
) D
WHERE D.Seq = 1

chrianth
Yak Posting Veteran

50 Posts

Posted - 2009-03-26 : 21:42:09

try this...

SELECT b.DATE, b.POINTS, b.ID, a.AMOUNT
FROM your_table a
JOIN (select date, max(points) maxPoints, max(ID) maxID
from your_table
group by date) b
ON(a.DATE = b.DATE and a.POINTS = b.maxPOINTS and a.ID = b.maxID)
ORDER BY 1

hope this helps...

aunabidi
Starting Member

11 Posts

Posted - 2009-03-26 : 21:59:31

quote:
Originally posted by vijayisonly

Maybe this?

SELECT D.Date,D.Points,D.ID,D.Amount FROM
(
SELECT Date,Points,ID,Amount,Row_Number() OVER (PARTITION BY Date ORDER BY Points,ID DESC) as Seq FROM <urtable>
) D
WHERE D.Seq = 1

Thanks for the reply. This is the error I get:

'Row_Number' is not a recognized function name.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-26 : 22:04:27

Are you not using SQL Server 2005?

aunabidi
Starting Member

11 Posts

Posted - 2009-03-26 : 22:05:34

quote:
Originally posted by chrianth

try this...

SELECT b.DATE, b.POINTS, b.ID, a.AMOUNT
FROM your_table a
JOIN (select date, max(points) maxPoints, max(ID) maxID
from your_table
group by date) b
ON(a.DATE = b.DATE and a.POINTS = b.maxPOINTS and a.ID = b.maxID)
ORDER BY 1

hope this helps...

Thanks for the reply. This is giving me multiple entries for all days. How can I fix that?

aunabidi
Starting Member

11 Posts

Posted - 2009-03-26 : 22:06:30

quote:
Originally posted by vijayisonly

Are you not using SQL Server 2005?

I am using SQL Server 2005.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-26 : 22:08:44

Can you post the exact query you tried?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-03-26 : 22:10:49

Check the compatibility level for your database, if it's set to 8.0 or lower you'll have to reset it to 9.0.

chrianth
Yak Posting Veteran

50 Posts

Posted - 2009-03-26 : 22:19:53

quote:
Originally posted by aunabidi

quote:
Originally posted by chrianth

try this...

SELECT b.DATE, b.POINTS, b.ID, a.AMOUNT
FROM your_table a
JOIN (select date, max(points) maxPoints, max(ID) maxID
from your_table
group by date) b
ON(a.DATE = b.DATE and a.POINTS = b.maxPOINTS and a.ID = b.maxID)
ORDER BY 1

hope this helps...

Thanks for the reply. This is giving me multiple entries for all days. How can I fix that?

Can you pull the records of the date that have duplicates and post it...let's have a look where we posibly missed... I can't see how it will have duplicates result if there is a unique ID within the same date...

aunabidi
Starting Member

11 Posts

Posted - 2009-03-26 : 22:21:24

quote:
Originally posted by vijayisonly

Can you post the exact query you tried?

SELECT D.dts,D.points,D.IDn,D.amt FROM
(
SELECT dts,points,IDn,amt,Row_Number() OVER (PARTITION BY dts ORDER BY points,IDn DESC) as Seq
FROM ptable) D
WHERE D.Seq = 1

aunabidi
Starting Member

11 Posts

Posted - 2009-03-26 : 22:24:40

quote:
Originally posted by robvolk

Check the compatibility level for your database, if it's set to 8.0 or lower you'll have to reset it to 9.0.

How do I check that? I am a developer for a company so I do not have rights to change or reset anything.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-26 : 22:31:28

Check this link..

http://msdn.microsoft.com/en-us/library/ms178653.aspx

ROW_NUMBER() function was introduced from SQL 2005 onwards and works only if your compatibility is set to 90.

aunabidi
Starting Member

11 Posts

Posted - 2009-03-26 : 22:32:10

quote:
Originally posted by chrianth

quote:
Originally posted by aunabidi

quote:
Originally posted by chrianth

try this...

SELECT b.DATE, b.POINTS, b.ID, a.AMOUNT
FROM your_table a
JOIN (select date, max(points) maxPoints, max(ID) maxID
from your_table
group by date) b
ON(a.DATE = b.DATE and a.POINTS = b.maxPOINTS and a.ID = b.maxID)
ORDER BY 1

hope this helps...

Thanks for the reply. This is giving me multiple entries for all days. How can I fix that?

Can you pull the records of the date that have duplicates and post it...let's have a look where we posibly missed... I can't see how it will have duplicates result if there is a unique ID within the same date...

aunabidi
Starting Member

11 Posts

Posted - 2009-03-26 : 22:34:19

quote:
Originally posted by vijayisonly

Check this link..

http://msdn.microsoft.com/en-us/library/ms178653.aspx

ROW_NUMBER() function was introduced from SQL 2005 onwards and works only if your compatibility is set to 90.

I do not have the rights to do this. Thanks for the help. I will find out more about this.

chrianth
Yak Posting Veteran

50 Posts

Posted - 2009-03-26 : 23:00:31

I am sorry I made a mistake in describing the problem. Instead of amount there is a column called Account. So the ID is unique for every account not on dates. So if account A1 has 4 entries it would have IDs 1,2,3,4. I want to display only the highest point entry for each day for each account.

Sorry for the mistake!

--So you have an account column. This might be the cause why there are duplicates.... do you want this account to display? if not, which record do you want to show if you have data for the account A1 and A2 as shown on the table below...

Date-------Points-------ID-----Amount-----Account
02/02/09-----4----------1--------10--------A1
02/02/09-----3----------2--------15--------A3
02/02/09-----4----------1--------12--------A2
02/02/09-----2----------4--------16--------A3
02/02/09-----2----------1--------20--------A3
02/02/09-----3----------2--------14--------A3

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-03-26 : 23:03:06

This should work (warning: not tested):

SELECT D.dts,D.points,D.IDn,D.amt FROM ptable D
WHERE EXISTS(SELECT TOP 1 * FROM ptable WHERE dts=D.dts and points=D.points and IDn=D.IDn order by points desc, IDn desc)

aunabidi
Starting Member

11 Posts

Posted - 2009-03-27 : 08:40:05

quote:
Originally posted by chrianth

I am sorry I made a mistake in describing the problem. Instead of amount there is a column called Account. So the ID is unique for every account not on dates. So if account A1 has 4 entries it would have IDs 1,2,3,4. I want to display only the highest point entry for each day for each account.

Sorry for the mistake!

--So you have an account column. This might be the cause why there are duplicates.... do you want this account to display? if not, which record do you want to show if you have data for the account A1 and A2 as shown on the table below...

Date-------Points-------ID-----Amount-----Account
02/02/09-----4----------1--------10--------A1
02/02/09-----3----------2--------15--------A3
02/02/09-----4----------1--------12--------A2
02/02/09-----2----------4--------16--------A3
02/02/09-----2----------1--------20--------A3
02/02/09-----3----------2--------14--------A3

Let me fix the IDs on that table because theyre unique for every account.

Date-------Points-------ID-----Amount-----Account
02/02/09-----4----------1--------10--------A1
02/02/09-----3----------1--------15--------A3
02/02/09-----4----------1--------12--------A2
02/02/09-----2----------2--------16--------A3
02/02/09-----2----------3--------20--------A3
02/02/09-----3----------4--------14--------A3

For that table I want the query to search for an account. Lets say the select statement asks for A3. The output should be:

02/02/09-----3----------4--------14--------A3

For A1 and A2 there is only one record each so its going to display that.

Thanks!

aunabidi
Starting Member

11 Posts

Posted - 2009-03-27 : 08:45:17

quote:
Originally posted by robvolk

This should work (warning: not tested):
SELECT D.dts,D.points,D.IDn,D.amt FROM ptable D
WHERE EXISTS(SELECT TOP 1 * FROM ptable WHERE dts=D.dts and points=D.points and IDn=D.IDn order by points desc, IDn desc)

This also gives me repeats. I made a mistake in posting the problem. Can you please see how that would affect this code?

Thanks!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-03-27 : 10:02:11

[code]SELECT D.dts,D.points,D.IDn,D.amt, D.Account FROM ptable D
WHERE EXISTS(SELECT TOP 1 * FROM ptable WHERE dts=D.dts and points=D.points and IDn=D.IDn and Account=D.Account order by points desc, IDn desc)[/code]If that still doesn't work you're going to have to post the actual output, and the exact results you need.

aunabidi
Starting Member

11 Posts

Posted - 2009-03-27 : 10:35:38

The problem is fixed. This is query that worked:

DECLARE @t table (
dt datetime
, points int
, id int
, amount int
, account char(2)
)

INSERT INTO @t (dt, points, id, amount, account)
SELECT '2009-02-02', 4, 1, 10, 'A1'
UNION SELECT '2009-02-02', 4, 1, 12, 'A2'
UNION SELECT '2009-02-02', 3, 1, 15, 'A3'
UNION SELECT '2009-02-02', 3, 2, 14, 'A3'
UNION SELECT '2009-02-03', 5, 3, 16, 'A3'
UNION SELECT '2009-02-03', 3, 4, 20, 'A3'

SELECT x.dt
, x.points
, x.id
, x.amount
, x.account
FROM @t As x
INNER
JOIN (
SELECT a.dt
, a.account
, Max(a.id) As max_id
FROM @t As a
INNER
JOIN (
SELECT dt
, account
, Max(points) As max_points
FROM @t
GROUP
BY dt
, account
) As b
ON a.dt = b.dt
AND a.account = b.account
AND a.points = b.max_points
GROUP
BY a.dt
, a.account
) As y
ON x.dt = y.dt
AND x.account = y.account
AND x.id = y.max_id

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources