I would like a query that picks up just 1 row per

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 2000 Forums

SQL Server Development (2000)

I would like a query that picks up just 1 row per

Author

Topic

bugulgad
Starting Member

21 Posts

Posted - 2008-02-14 : 20:49:53

use northwind
go

create table table1
(
A char(10),
B char(10),
C char(10)
)

insert table1
values('Niners', 'Gold', 'WATCH')
insert table1
values('Niners', 'Silver', 'watch')
insert table1
values('Niners', 'Bronze', 'w@tch')
insert table1
values('Patriots', 'Silver', 'watch')
insert table1
values('Patriots', 'Bronze', 'w@tch')
insert table1
values('Giants', 'Bronze', 'w@tch')

-- truncate table table1
--drop table table1

select *
from table1

-- I would like a query that picks up just 1 row per unique value in col A
--The row it should pick should be based on a ranked criteria of possible values in col B
-- Assuming the ranking is Gold, Silver then Bronze, result set should be 3 rows looking like this
--Niners, Gold, WATCH
--Patriots, Silver, watch
--Giants, Bronze, w@tch

pdreyer
Starting Member

6 Posts

Posted - 2008-02-15 : 02:26:06

[code]
select * into #table2 from
(select 1,'Gold' union all
select 2,'Silver' union all
select 3,'Bronze'
)r(D,B)

select a.* from table1 a
join #table2 b
on a.B=b.B
join (
select a.A,min(b.D)D from table1 a
join #table2 b
on a.B=b.B
group by a.A
)c
on a.A=c.A
and b.D=c.D
order by b.D

drop table #table2
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-15 : 02:42:44

[code]-- Prepare sample data
DECLARE @Sample TABLE (ColA VARCHAR(10), ColB VARCHAR(10), ColC VARCHAR(10))

INSERT @Sample
SELECT 'Niners', 'Gold', 'WATCH' UNION ALL
SELECT 'Niners', 'Silver', 'watch' UNION ALL
SELECT 'Niners', 'Bronze', 'w@tch' UNION ALL
SELECT 'Patriots', 'Silver', 'watch' UNION ALL
SELECT 'Patriots', 'Bronze', 'w@tch' UNION ALL
SELECT 'Giants', 'Bronze', 'w@tch'

-- Show the expected output
SELECT s.ColA,
s.ColB,
s.ColC
FROM @Sample AS s
INNER JOIN (
SELECT ColA,
MIN( CASE ColB
WHEN 'Gold' THEN 1
WHEN 'Silver' THEN 2
WHEN 'Bronze' THEN 3
ELSE 9
END) AS Medal
FROM @Sample
GROUP BY ColA
) AS x ON x.ColA = s.ColA
WHERE CASE x.Medal
WHEN 1 THEN 'Gold'
WHEN 2 THEN 'Silver'
WHEN 3 THEN 'Bronze'
ELSE NULL
END = s.ColB
ORDER BY x.Medal[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources