Selecting unique columns - 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

Selecting unique columns

Author

Topic

sanjeevka08
Starting Member

18 Posts

Posted - 2012-09-20 : 06:29:29

I have an SP in which i am selecting a number of columns from 5 tables using join condition. i want to filter the resulting table such that if for number of rows, data in a particular column called 'date' appears same, i have to choose only only one row that having maximum ID amoung that rows. please give an appropraite query

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2012-09-20 : 06:31:45

Can you post sample data and expected output?

Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-20 : 11:52:00

something like


SELECT required columns here
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY yourdatecolumn ORDER BY ID DESC) AS Seq,...
rest of the query here
)t
WHERE Seq=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sanjeevka08
Starting Member

18 Posts

Posted - 2012-09-20 : 23:41:49

item date price quantity
--------------------------------------------
talc - 12/12/2011 - 56 - 6
soap - 01/04/2012 - 34 - 8
soap - 01/04/2012 - 38 - 8
soap - 02/04/2012- 14 - 9
oil - 01/04/2012 - 89 - 8

consider this as a sample table. here u can see for the item soap, there are 2 rows with same dates, but price is different. i want to filter the table like if for an item, date comes same in two rows for a particular item, then i want to choose the row with highest price and eliminate other. How can i write a query for that?? please keep in mind, that i want to show the other items in which dates are diffrent

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-09-21 : 03:00:16

Hi sanjeev,

I just modified visakh's generalized code here

DECLARE @tab TABLE (item varchar(10), date datetime2, price dec(6,2), quantity int)
INSERT INTO @tab VALUES
('talc', '12/12/2011', 56, 6),
('soap', '01/04/2012', 34, 8),
('soap', '01/04/2012', 38, 8),
('soap', '02/04/2012', 14, 9),
('oil', '01/04/2012', 89, 8)

SELECT item, DATE, price, quantity
FROM
(
SELECT item, DATE, price, quantity, ROW_NUMBER() OVER (PARTITION BY item,date ORDER BY quantity DESC) AS Seq
FROM @tab
)t
WHERE Seq=1;

Thanks to visakh

Thanks to Visakh also...

--
Chandu

sanjeevka08
Starting Member

18 Posts

Posted - 2012-09-21 : 04:37:34

Thanks..

Thats working well

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-09-21 : 05:58:32

Welcome

--
Chandu

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-09-21 : 05:58:33

Welcome

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-21 : 12:25:33

quote:
Originally posted by bandi

Hi sanjeev,

I just modified visakh's generalized code here

DECLARE @tab TABLE (item varchar(10), date datetime2, price dec(6,2), quantity int)
INSERT INTO @tab VALUES
('talc', '12/12/2011', 56, 6),
('soap', '01/04/2012', 34, 8),
('soap', '01/04/2012', 38, 8),
('soap', '02/04/2012', 14, 9),
('oil', '01/04/2012', 89, 8)

SELECT item, DATE, price, quantity
FROM
(
SELECT item, DATE, price, quantity, ROW_NUMBER() OVER (PARTITION BY item,date ORDER BY quantity DESC) AS Seq
FROM @tab
)t
WHERE Seq=1;

Thanks to visakh

Thanks to Visakh also...

--
Chandu

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources