Select from the last 7 days and using MAX

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

Select from the last 7 days and using MAX

Author

Topic

wkm1925
Posting Yak Master

207 Posts

Posted - 2008-05-08 : 21:14:16

I've have these following table
tbllocation
Main_ID | Date_Taken | Time |Hit
-----------------------------------------
206 | 5/9/2008 | 100 | 2
206 | 5/9/2008 | 200 | 3
206 | 5/6/2008 | 300 | 6
201 | 5/1/2008 | 400 | 5
201 | 5/4/2008 | 500 | 9
201 | 5/7/2008 | 600 | 2
204 | 5/2/2008 | 700 | 2
204 | 5/3/2008 | 800 | 4
204 | 5/6/2008 | 900 | 2
203 | 5/7/2008 | 100 | 2
203 | 5/8/2008 | 200 | 3
203 | 5/9/2008 | 300 | 6
202 | 5/4/2008 | 400 | 5
202 | 5/3/2008 | 500 | 9
202 | 5/8/2008 | 200 | 3
205 | 5/2/2008 | 300 | 6
205 | 5/1/2008 | 400 | 5
205 | 5/9/2008 | 500 | 9

tblSetValue
Main_ID | Hit2
---------------
206 | 3
201 | 5
204 | 3
203 | 1
202 | 8
205 | 7
*Main_ID is a primary key

Condition
1. Let's say, the current date is 5/9/2008
2. Result only display the last 7 days data. From above data. it's mean only pickup from 5/3/2008 to 5/9/2008
3. Every Main_ID only pickup the MAX Hit
4. Diff (column on the fly) = Hit - Hit2

The expected result shown as follow
tblResult
Main_ID | Date_Taken | Time | Hit | Hit2 | Diff
-----------------------------------------------
206 | 5/6/2008 | 300 | 6 | 3 | 3
201 | 5/4/2008 | 500 | 9 | 5 | 4
204 | 5/3/2008 | 800 | 4 | 3 | 1
203 | 5/9/2008 | 300 | 6 | 1 | 5
....
....
....

Anyone can help me to built the query?

eralper
Yak Posting Veteran

66 Posts

Posted - 2008-05-09 : 01:45:00

Hello,

You can read the article http://www.kodyaz.com/articles/top-n-random-rows-foreach-category-or-group.aspx for selecting top n rows for each group on a data set.

For your case you can select top 1 ordered by max for each main_id using the method summarized in the article.

The main thing is using the ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) syntax.

You can partition your results due to MAIN_ID and order them by HIT number. This will give you a row number and you can select only the rows with row number equal to 1.

Of course you should eliminate the records out of the 7 days range prior taking the row number in a where clause.

I hope this helps,

Eralper
http://www.kodyaz.com

-------------
Eralper
http://www.kodyaz.com

wkm1925
Posting Yak Master

207 Posts

Posted - 2008-05-09 : 07:49:47

do you have more simple explanation and example?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-09 : 07:57:38

[code]SELECT t1.Main_ID,
t1.Date_Taken,
t1.Time,
t1.Hit,
x.Hit2,
t1.Hit - x.Hit2 AS Diff
FROM tblLocation AS t1
INNER JOIN (
SELECT Main_ID,
MAX(Hit) AS maxHit
FROM tblLocation
WHERE Date_Taken >= DATEADD(DAY, -7, GETDATE())
) AS t2 ON t2.Main_ID = t1.Main_ID
INNER JOIN tblSetValue AS x ON x.Main_ID = t1.Main_ID
WHERE t2.maxHit = t1.Hit
AND t1.Date_Taken >= DATEADD(DAY, -7, GETDATE())[/code]

E 12°55'05.25"
N 56°04'39.16"

wkm1925
Posting Yak Master

207 Posts

Posted - 2008-05-09 : 10:26:24

Kindest Mr. Peso,

Your query sample help me a lot. How about if the table shown as follow,
Main_ID | Date_Taken | Time |Hit
-----------------------------------------
206 | 5/9/2008 | 100 | 2
206 | 5/9/2008 | 200 | 3
206 | 5/9/2008 | 300 | 6
206 | 5/9/2008 | 400 | 5
206 | 5/8/2008 | 100 | 9
206 | 5/8/2008 | 200 | 2
206 | 5/8/2008 | 300 | 2
206 | 5/8/2008 | 400 | 4
204 | 5/9/2008 | 100 | 2
204 | 5/9/2008 | 200 | 3
204 | 5/9/2008 | 300 | 2
204 | 5/9/2008 | 400 | 2
204 | 5/8/2008 | 100 | 2
204 | 5/8/2008 | 200 | 3
204 | 5/8/2008 | 300 | 2
204 | 5/8/2008 | 400 | 2
...
...
...

We can see, 1 Main_ID have a many Date_Taken, 1 Date_Taken have many Time.

In 1 day, just cater the MAX hit? The expected result shown as follow
tblResult
Main_ID | Date_Taken | Time | Hit
------------------------------------
206 | 5/9/2008 | 300 | 6
206 | 5/8/2008 | 100 | 9
204 | 5/9/2008 | 200 | 3
204 | 5/8/2008 | 200 | 3
...
...
...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-09 : 10:34:13

quote:
Originally posted by wkm1925

Kindest Mr. Peso,

Your query sample help me a lot. How about if the table shown as follow,
Main_ID | Date_Taken | Time |Hit
-----------------------------------------
206 | 5/9/2008 | 100 | 2
206 | 5/9/2008 | 200 | 3
206 | 5/9/2008 | 300 | 6
206 | 5/9/2008 | 400 | 5
206 | 5/8/2008 | 100 | 9
206 | 5/8/2008 | 200 | 2
206 | 5/8/2008 | 300 | 2
206 | 5/8/2008 | 400 | 4
204 | 5/9/2008 | 100 | 2
204 | 5/9/2008 | 200 | 3
204 | 5/9/2008 | 300 | 2
204 | 5/9/2008 | 400 | 2
204 | 5/8/2008 | 100 | 2
204 | 5/8/2008 | 200 | 3
204 | 5/8/2008 | 300 | 2
204 | 5/8/2008 | 400 | 2
...
...
...

We can see, 1 Main_ID have a many Date_Taken, 1 Date_Taken have many Time.

In 1 day, just cater the MAX hit? The expected result shown as follow
tblResult
Main_ID | Date_Taken | Time | Hit
------------------------------------
206 | 5/9/2008 | 300 | 6
206 | 5/8/2008 | 100 | 9
204 | 5/9/2008 | 200 | 3
204 | 5/8/2008 | 200 | 3
...
...
...

If you're using sql 2000

SELECT t1.Main_ID,t1.Date_Taken,t1.Time,t1.Hit
FROM YourTable t1
INNER JOIN (SELECT Main_ID ,Date_Taken ,MAX(Hit) AS MaxHit
            FROM YourTable
            GROUP BY Main_ID ,Date_Taken)t2
ON t2.Main_ID = t1.Main_ID
AND t2.Date_Taken=t1.Date_Taken
AND t2.MaxHit=t1.Hit

And if sql 2005:-

SELECT t.Main_ID,t.Date_Taken,t.Time,t.Hit
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY Main_ID ,Date_Taken ORDER BY Hit DESC) AS RowNo,
*
FROM YourTable)t
WHERE t.RowNo=1

wkm1925
Posting Yak Master

207 Posts

Posted - 2008-05-10 : 01:23:52

Kindest mr. eralper, mr. peso and mr. visakh16,

tq to all of you. your info is really great.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources