Can you help with my query? - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Can you help with my query?

Author

Topic

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2008-02-21 : 00:50:48

My query is looking for unique booking numbers with their product name, however the database where the data is stored seems to have (in some cases) more than one product Code.
How can i select just the first one it comes to for each "booking No"?
So that for the first "Booking No" A000398, it will only show
A000398 WIEDU02 European Explorer

and NOT

A000398 WIEDU02 European Explorer
A000398 WIEPU02 European Explorer

THX ICW

Select distinct "Booking No", "Item ref" as ProdCode, "Prod Name" from tourproducts, BComponents where  bcomponents."Item Ref" = tourproducts."Tour Product Code"
and "Product Type" = 'T'  and "PaxID" = '001' and "Tour Prod Status" = 'CF'
order by "Booking No"

Sample Results

Booking ProdCode Prod Name
A000398 WIEDU02 European Explorer
A000398 WIEPU02 European Explorer
A000678 WHARU02 Heart of Italy
A000840 WIRIU02 Real Ireland
A001062 WROLU02 European Jaunt
A001097 WROLU02 European Jaunt
A001123 WIVAU02 European Value
A001164 WITMU02 Real Italy
A001305 WSOMU02 Sound of Music
A001450 WIPMU02 Portugal Morocco and Spain
A001450 WISPU02 Real Spain
A001745 EGVOU03 Egyptian Voyager
A001745 PREU8U01Pre Registered Tours Europe Summer 2008
A001745 WIEVU02 Egyptian Voyager
A002279 DCRAU03 Canadas Rockies
A002419 WIRIU02 Real Ireland
A002455 WIRIU02 Real Ireland
A002500 WITMU02 Real Italy
A002553 WHARU02 Heart of Italy
A002592 WISPU02 Real Spain
A002762 WFESU02 French Escapade
A002842 WIPMU02 Portugal Morocco and Spain
A002842 WISPU02 Real Spain
A002915 RITMU102Real Italy Reverse
A002922 DPNAU03 Northwest Coastal Adventure
A003004 AGBOU03 Best of Greece
A003004 WIRGU02 Real Greece
A003026 DWFTU03 Western Frontiers
A003067 WISIU02 Simply Italy
A003067 WITAU02 Italy and Sicily

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-02-21 : 01:01:16

Try
select * from
(
Select [Booking No], [Item ref] as ProdCode, [Prod Name],row_number()over (partition by [Booking No] order by [Booking No]) as [Row]
from tourproducts, BComponents where bcomponents."Item Ref" = tourproducts."Tour Product Code"
and [Product Type] = 'T' and [PaxID] = '001' and [Tour Prod Status] = 'CF' order by [Booking No] )b where [Row]=1

One more suggestion, use [] instead of quotes for aliases and specifying column names.

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2008-02-21 : 01:47:45

Thanks for the tip about the square brackets. Just so I know for future ref why should i not use quotes.

Also the query you offered gives me the following error.
Msg 1033, Level 15, State 1, Line 5
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified

thanks
ICW

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-02-21 : 01:51:33

Try
select * from
(
Select [Booking No], [Item ref] as ProdCode, [Prod Name],row_number()over (partition by [Booking No] order by [Booking No]) as [Row]
from tourproducts, BComponents where bcomponents."Item Ref" = tourproducts."Tour Product Code"
and [Product Type] = 'T' and [PaxID] = '001' and [Tour Prod Status] = 'CF' ) b where [Row]=1 order by [Booking No]

for quotes, see following thread:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=95347

MaxLu
Starting Member

1 Post

Posted - 2008-02-21 : 02:13:38

Select distinct "Booking No",
(select top 1 "Item ref" from BComponents where bcomponents."Item Ref" = tourproducts."Tour Product Code" ) as ProdCode,
"Prod Name"
from tourproducts
where "Product Type" = 'T' and "PaxID" = '001' and "Tour Prod Status" = 'CF'
order by "Booking No"

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2008-02-21 : 04:21:07

Thanks both for these queries;

Sunil yours works perfectly now and gives me 1471 rows.

I wanted to compare the two queries to see if they gave the same result,
but MAX your query s giving an error, because it looks like the second table is not a part of the query. Also , it may be a red herrig but i want to get the top Booking No not the top Item ref.
Thanks again guys
much appreciated

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-02-21 : 04:33:09

what's MAX you are trying to get? Also, this
select * from
(
Select [Booking No], [Item ref] as ProdCode, [Prod Name],row_number()over (partition by [Booking No] order by [Booking No]) as [Row]
from tourproducts, BComponents where bcomponents."Item Ref" = tourproducts."Tour Product Code"
and [Product Type] = 'T' and [PaxID] = '001' and [Tour Prod Status] = 'CF' ) b where [Row]=1 order by [Booking No]

will give you first booking number it finds.
for example:
A001450 WIPMU02 Portugal Morocco and Spain
A001450 WISPU02 Real Spain

Above query will return first record i.e. Portugal Morocco and Spain.
Hope it helps.

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2008-02-21 : 04:44:26

Sunil,
I have adopted your one into my query which I used for a sql view

thanks again:O)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-21 : 06:34:55

[code]SELECT {Table alias tp or bc here}.[Booking No],
bc.[Item ref] AS ProdCode,
MAX({Table alias tp or bc here}.[Prod Name]) AS [Prod Name]
FROM TourProducts AS tp
INNER JOIN BComponents AS bc ON bc.[Item Ref] = tp.[Tour Product Code]
WHERE {Table alias tp or bc here}.[Product Type] = 'T'
AND {Table alias tp or bc here}.PaxID = '001'
AND {Table alias tp or bc here}.[Tour Prod Status] = 'CF'
order by {Table alias tp or bc here}.[Booking No],
bc.[Item ref][/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