Select particular rows from a table by a criteria

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 particular rows from a table by a criteria

Author

Topic

skybvi
Posting Yak Master

193 Posts

Posted - 2011-02-10 : 14:31:16

I want to do a modification in the new table which was a result of the join query....
..i want only tht rows in which pricedate(column) is equal to date_sold or nearest to the date_sold( lesser).......

I mean like if there are 5 rows like this( in the table) ---

item des qty date_ sold so_p c_p t_s sa_p price pricedate

1)SD Soda 1 '2010-09-01 08:32:42' 10$ 11$ 20$ 0$ 11$ '2010-09-03 13:21:33'
2)SD Soda 1 '2010-09-01 08:32:42' 10$ 11$ 20$ 0$ 10$ '2010-08-27 11:12:13'
3)SD Soda 1 '2010-09-01 08:32:42' 10$ 11$ 20$ 0$ 13$ '2011-02-01 10:43:44'
4)SD Soda 1 '2010-09-01 08:32:42' 10$ 11$ 20$ 0$ 08$ '2010-05-09 12:12:23'
5)SD Soda 1 '2010-09-01 08:32:42' 10$ 11$ 20$ 0$ 12$ '2010-07-29 17:15:34'

So, in the above rows from my data, only 1 row should be picked tht is row 2)
because the pricedate ('2010-08-27 11:12:13) is nearest to the date_sold(2010-09-01 08:32:42) in the backward direction,
In row 1) '2010-09-03 is nearest to '2010-09-01 but I don't want that as it is ahead of '2010-09-01....

I hope it was clear.

For a particular item and a particular date_sold, I want only 1 row(rows in which pricedate(column) is equal to date_sold or nearest to the date_sold( lesser))
I have many items (2000) like that in my table.
Total rows are around 16k.

Regards,
Sushant

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-02-10 : 14:55:14

[code]DECLARE @Sample TABLE
(
RowID INT,
aDate DATETIME,
bDate DATETIME
)

INSERT @Sample
SELECT 1, '2010-09-01 08:32:42', '2010-09-03 13:21:33' UNION ALL
SELECT 2, '2010-09-01 08:32:42', '2010-08-27 11:12:13' UNION ALL
SELECT 3, '2010-09-01 08:32:42', '2011-02-01 10:43:44' UNION ALL
SELECT 4, '2010-09-01 08:32:42', '2010-05-09 12:12:23' UNION ALL
SELECT 5, '2010-09-01 08:32:42', '2010-07-29 17:15:34'

;WITH cte(RowID, aDate, bDate, YakDiff)
AS (
SELECT RowID,
aDate,
bDate,
DATEDIFF(SECOND, aDate, bDate) AS YakDiff
FROM @Sample
)
SELECT TOP(1) WITH TIES
RowID,
aDate,
bDate
FROM cte
ORDER BY SIGN(YakDiff),
YakDiff DESC[/code]

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-02-10 : 14:59:58

[code]DECLARE @Sample TABLE
(
RowID INT,
Article CHAR(1),
aDate DATETIME,
bDate DATETIME
)

INSERT @Sample
SELECT 01, 'A', '2010-09-01 08:32:42', '2010-09-03 13:21:33' UNION ALL
SELECT 02, 'A', '2010-09-01 08:32:42', '2010-08-27 11:12:13' UNION ALL
SELECT 03, 'A', '2010-09-01 08:32:42', '2011-02-01 10:43:44' UNION ALL
SELECT 04, 'A', '2010-09-01 08:32:42', '2010-05-09 12:12:23' UNION ALL
SELECT 05, 'A', '2010-09-01 08:32:42', '2010-07-29 17:15:34' UNION ALL
SELECT 11, 'B', '2010-09-01 08:32:42', '2010-09-03 13:21:33' UNION ALL
SELECT 12, 'B', '2010-09-01 08:32:42', '2010-08-27 11:12:13' UNION ALL
SELECT 13, 'B', '2010-09-01 08:32:42', '2011-02-01 10:43:44' UNION ALL
SELECT 14, 'B', '2010-09-01 08:32:42', '2010-05-09 12:12:23' UNION ALL
SELECT 15, 'B', '2010-09-01 08:32:42', '2010-07-29 17:15:34'

;WITH cte(RowID, Article, aDate, bDate, SeqID)
AS (
SELECT RowID,
Article,
aDate,
bDate,
DENSE_RANK() OVER (PARTITION BY Article ORDER BY DATEDIFF(SECOND, aDate, bDate) DESC) AS SeqID
FROM @Sample
WHERE DATEDIFF(SECOND, aDate, bDate) <= 0
)
SELECT RowID,
Article,
aDate,
bDate
FROM cte
WHERE SeqID = 1[/code]

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

skybvi
Posting Yak Master

193 Posts

Posted - 2011-02-10 : 15:03:26

@ peso
Thanks a lot,..

i got the 2nd row as a result of tht query,.
Now, what code should i run so that i can get similiar results for all the rows in my table..?
The same type of data(which i mentioned)is like for 2000 items in my table.
The total no of rows are 125000

Regards,
Sushant

Regards,
Sushant
DBA
West Indies

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-02-10 : 15:43:40

See my answer posted 02/10/2011 : 14:59:58

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

skybvi
Posting Yak Master

193 Posts

Posted - 2011-02-10 : 15:50:17

@ peso...
U put up other 5 rows.
How could i input my data from the table to the place where u put up the sample data...?
I have a table with 1,25,000 rows of data similiar to wat i had mentioned.
Do i have to put it manually only, isnt there any code tht could put it all the sample data and then run it to show the results...
Did you get wat i meant?

Thanks.

Regards,
Sushant
DBA
West Indies

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-02-10 : 15:52:42

[code];WITH cteSource
AS (
SELECT *
DENSE_RANK() OVER (PARTITION BY SomeColumn ORDER BY DATEDIFF(SECOND, aDate, bDate) DESC) AS SeqID
FROM dbo.YourTableNameHere
WHERE aDate < bDate
)
SELECT *
FROM cteSource
WHERE SeqID = 1[/code]

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

skybvi
Posting Yak Master

193 Posts

Posted - 2011-02-10 : 15:58:59

thanks peso,

I ran tht query, i got error:
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'DENSE_RANK'.

Regards,
Sushant
DBA
West Indies

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-02-10 : 16:26:49

[code];WITH cteSource
AS (
SELECT *,
DENSE_RANK() OVER (PARTITION BY SomeColumn ORDER BY DATEDIFF(SECOND, aDate, bDate) DESC) AS SeqID
FROM dbo.YourTableNameHere
WHERE aDate < bDate
)
SELECT *
FROM cteSource
WHERE SeqID = 1[/code]

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

skybvi
Posting Yak Master

193 Posts

Posted - 2011-02-11 : 08:59:04

@ peso...
In hte above script...partition by somecolumn....
the somecolumn should be item or date_sold or pricedate..
I am confused.

Regards,
Sushant

Regards,
Sushant
DBA
West Indies

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-02-11 : 13:34:35

SomeColumn is your Item column.
I strongly suggest you read Books Online about ROW_NUMBER() function.

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

skybvi
Posting Yak Master

193 Posts

Posted - 2011-02-11 : 13:53:14

@ peso...
I got the results as i wanted.
U r a genious :)

Thxs a lot.

Regards,
Sushant
DBA
West Indies

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources