Help with select - 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

Help with select

Author

Topic

maevr
Posting Yak Master

169 Posts

Posted - 2010-03-24 : 09:11:34

I would like the compare the different columns (forb) and fetch the one that has the highest value for each id.

create table #ForbTable(
id int,
forb1 int null,
forb2 int null,
forb3 int null,
forb4 int null
primary key(id)
)

insert into #ForbTable(id,forb1,forb2,forb3,forb4) values(1,10,2,1,20)
insert into #ForbTable(id,forb1,forb2,forb3,forb4) values(2,3,13,12,1)
insert into #ForbTable(id,forb1,forb2,forb3,forb4) values(3,7,10,55,5)
insert into #ForbTable(id,forb1,forb2,forb3,forb4) values(91,90,10,12,21)

Expected output:
1 forb4
2 forb2
3 forb3
4 forb1

Should I use a case?

//Maevr

binto
Yak Posting Veteran

59 Posts

Posted - 2010-03-24 : 09:47:27

can you please try
SELECT test.id,test.ColumnName
FROM (
SELECT id,forb1,forb2,forb3,forb4
FROM #ForbTable) t
UNPIVOT
(Colname FOR ColumnName IN (forb1,forb2,forb3,forb4)
) test

Thanks & Regards
Binto Thomas

maevr
Posting Yak Master

169 Posts

Posted - 2010-03-24 : 10:06:26

Thans for fast reply.
The output for your select is:

1 forb1
1 forb2
1 forb3
1 forb4
2 forb1
2 forb2
2 forb3
2 forb4
3 forb1
3 forb2
3 forb3
3 forb4
4 forb1
4 forb2
4 forb3
4 forb4

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-03-24 : 11:12:24

quote:
Originally posted by maevr

I would like the compare the different columns (forb) and fetch the one that has the highest value for each id.

create table #ForbTable(
id int,
forb1 int null,
forb2 int null,
forb3 int null,
forb4 int null
primary key(id)
)

insert into #ForbTable(id,forb1,forb2,forb3,forb4) values(1,10,2,1,20)
insert into #ForbTable(id,forb1,forb2,forb3,forb4) values(2,3,13,12,1)
insert into #ForbTable(id,forb1,forb2,forb3,forb4) values(3,7,10,55,5)
insert into #ForbTable(id,forb1,forb2,forb3,forb4) values(91,90,10,12,21)

Expected output:
1 forb4
2 forb2
3 forb3
4 forb1

Should I use a case?

//Maevr

How are you getting this expected output? What is the logic?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-24 : 11:24:58

[code]
SELECT id,ColumnName
FROM
(
SELECT test.id,test.ColumnName,Val,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY Val DESC) AS Seq
FROM (
SELECT id,forb1,forb2,forb3,forb4
FROM #ForbTable) t
UNPIVOT
(Val FOR ColumnName IN (forb1,forb2,forb3,forb4)
) test
)r
WHERE Seq=1
[/code]

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

maevr
Posting Yak Master

169 Posts

Posted - 2010-03-25 : 02:45:17

Thanks visakh16!

I'm having trouble understanding your query, can you please explain?

maevr
Posting Yak Master

169 Posts

Posted - 2010-03-25 : 03:47:44

Another question.

How can I get the value from the field that has the highest value returned in the same query?

example:
id column value
1 forb4 20
2 forb2 13
3 forb3 55
4 forb1 90

maevr
Posting Yak Master

169 Posts

Posted - 2010-03-25 : 06:00:10

I managed to solve the issue myself regarding my last post, thanks for all the help.

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-25 : 06:11:37

quote:
Originally posted by maevr

I managed to solve the issue myself regarding my last post, thanks for all the help.

could you please show us,How did you solve it?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-25 : 10:35:25

quote:
Originally posted by maevr

Thanks visakh16!

I'm having trouble understanding your query, can you please explain?

i just added a rownumber function in your unpivoted query to number records for each id group based on the decreasing order of Val. This will cause records within each ID value group to be numbered 1,2,.. starting from largest value of Val. then retrieving values of records with row number as 1 will ensure you get always records with max value of Val within each ID group

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

maevr
Posting Yak Master

169 Posts

Posted - 2010-03-30 : 01:56:49

Hi again!
What do I do if there is two (or more) with the same number?
example:
insert into #ForbTable(id,forb1,forb2,forb3,forb4) values(5,20,2,1,20)

Can I separate them using a '/' so that the response will be:
5 forb1/forb4

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-30 : 13:12:01

quote:
Originally posted by maevr

Hi again!
What do I do if there is two (or more) with the same number?
example:
insert into #ForbTable(id,forb1,forb2,forb3,forb4) values(5,20,2,1,20)

Can I separate them using a '/' so that the response will be:
5 forb1/forb4


;With CTE(id,ColumnName,Val,Seq)
AS
(
SELECT test.id,test.ColumnName,Val,
DENSE_RANK() OVER (PARTITION BY id ORDER BY Val DESC) 
FROM (
SELECT id,forb1,forb2,forb3,forb4
FROM #ForbTable) t
UNPIVOT
(Val FOR ColumnName IN (forb1,forb2,forb3,forb4)
) test
)r
)

SELECT id,
STUFF((SELECT '/'+ColumnName FROM CTE WHERE id=c.id AND Seq=1 FOR XML PATH('')),1,1,'') AS ValList
FROM CTE c
WHERE Seq=1

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

maevr
Posting Yak Master

169 Posts

Posted - 2010-04-01 : 04:47:18

Cannot get the last query to work.
Copy/paste using the temporary table/data in the first post.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-01 : 05:01:26

[code]
create table #ForbTable(
id int,
forb1 int null,
forb2 int null,
forb3 int null,
forb4 int null
primary key(id)
)

insert into #ForbTable(id,forb1,forb2,forb3,forb4) values(1,10,2,1,20)
insert into #ForbTable(id,forb1,forb2,forb3,forb4) values(2,3,13,12,1)
insert into #ForbTable(id,forb1,forb2,forb3,forb4) values(3,7,10,55,5)
insert into #ForbTable(id,forb1,forb2,forb3,forb4) values(91,90,10,12,21)
insert into #ForbTable(id,forb1,forb2,forb3,forb4) values(5,20,2,1,20)

;With CTE(id,ColumnName,Val,Seq)
AS
(
SELECT test.id,test.ColumnName,Val,
DENSE_RANK() OVER (PARTITION BY id ORDER BY Val DESC)
FROM (
SELECT id,forb1,forb2,forb3,forb4
FROM #ForbTable) t
UNPIVOT
(Val FOR ColumnName IN (forb1,forb2,forb3,forb4)
) test

)

SELECT DISTINCT id,
STUFF((SELECT '/'+ColumnName FROM CTE WHERE id=c.id AND Seq=1 FOR XML PATH('')),1,1,'') AS ValList
FROM CTE c
WHERE Seq=1

DROP TABLE #ForbTable

output
------------------------------
id ValList
1 forb4
2 forb2
3 forb3
5 forb1/forb4
91 forb1

[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources