How to retrieve some rows from grouped query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

How to retrieve some rows from grouped query?

Author

Topic

hornet
Yak Posting Veteran

96 Posts

Posted - 2007-06-08 : 14:19:19

Hi all!

I'm to do next task. I have a query which finds the best suggestions by the flight. User before chooses how many tickets he's interested. And Procedure firstly finds all tickets in DB and after that she should select top (countTickets) from SELECT into the procedure.
e.g.

create procedure ...
@AdiltsTickets
@ChildTickets

select * from tickets where .... group by FlightDate, FlightNumber

here i'm to select the best variants ( @AdiltsTickets + @ChildTickets)
how to select

I was thinking of SELECT TOP, but AFAIK TOP can't work with not constants.

Does anybody can offer me excluding treating outside DB?

Thanks in advance!

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-08 : 14:31:41

I didnt understand your question. Can you provide sample data and expected output so its easier..?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

hornet
Yak Posting Veteran

96 Posts

Posted - 2007-06-08 : 16:07:11

I have a proc
create proc FindTickets
@AdultTickets int,
@ChildTickets int,
@flightDate int,
@salonType int
as
select numberFlight, dateFlight, cost into #tmpTickets from Flights
where flightDate = @flightDate and salonType = @salonType

...
and i'm to check are there needed quantity of tickets
( @AdiltsTickets + @ChildTickets) into temp table before exit.
how do it?

there is solving this subj being treating data on client, but wouldn't like it

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-08 : 16:14:09

Sorry I am still unable to understand your question..can you post some sample data in your table and the expected output.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-06-08 : 16:21:33

so you want top N rows for each group?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

hornet
Yak Posting Veteran

96 Posts

Posted - 2007-06-08 : 16:31:55

yea, i wanna select top @adultTickets + @childTickets from #table
indeed )
How it possible in T-SQL?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-09 : 01:02:18

Set Rowcount @adultTickets + @childTickets
Select columns from #table
Set rowcount 0

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources