Procedure with Multilpe result sets

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)

Procedure with Multilpe result sets

Author

Topic

Shilpa22
Starting Member

37 Posts

Posted - 2010-04-24 : 08:54:39

Hi All,
I have procedure in which I have 1 select statement which returns a set of rows.
Ex result set:
AccountName StartDate
aaa 22/04/10
bbb 23/04/10
ccc 24/04/10
ddd 15/04/10
eee 26/04/10
Now from the above result set I need a row which have MIN(StartDate).
I need both these results. Using C# I need to retrieve this data.

Can anyone please help me.give me an example. How to achieve this..

Thanks,
Shilpa

Thanks in Advance
Shilpa

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-24 : 08:58:28

[code]SELECT * FROM Table WHERE StartDate IN (SELECT MIN(StartDate) FROM Table)[/code]

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

Shilpa22
Starting Member

37 Posts

Posted - 2010-04-24 : 09:02:12

Hi,
Thanks for your reply.
select * from table meaning???
First result set is a dynamic result set. I need to query on that resultset. How can I do that?

Thanks

Thanks in Advance
Shilpa

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-24 : 09:12:55

which dynamic set?

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

Shilpa22
Starting Member

37 Posts

Posted - 2010-04-24 : 09:24:35

With the first select statement, I will get a dynamic result set which luks like below,
AccountName StartDate
aaa 22/04/10
bbb 23/04/10
ccc 24/04/10
ddd 15/04/10
eee 26/04/10
Again I need MIN(StartDate) from the above result. SO totally I want prcedure to return the set of account names (first result set) and also the MIN(DATE).

Pls help me..

Thanks in Advance
Shilpa

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-24 : 09:31:05

whats the current query you're using?

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

Shilpa22
Starting Member

37 Posts

Posted - 2010-04-24 : 09:52:06

SELECT account_id,
query_start_time
FROM (SELECT account_id,
query_start_time,
Row_number() OVER(PARTITION BY account_id ORDER BY query_start_time) rn
FROM dws..dws_job_query where account_id like '%Dss_stage%') t
WHERE rn = 1

It returns the first result set. Based on this I need to get MIN(Date) row

Thanks in Advance
Shilpa

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-24 : 11:10:20

[code]
SELECT account_id,
query_start_time,
MinDate
FROM (SELECT account_id,
query_start_time,
Row_number() OVER(PARTITION BY account_id ORDER BY query_start_time) rn,
MIN(query_start_time) OVER () MinDate
FROM dws..dws_job_query where account_id like '%Dss_stage%') t
WHERE rn = 1
[/code]

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

waterduck
Aged Yak Warrior

982 Posts

Posted - 2010-04-24 : 14:05:38

[code]
SELECT account_id,
MIN(query_start_time)
FROM dws..dws_job_query
WHERE account_id like '%Dss_stage%'
GROUP BY account_id
[/code]

Hope can help...but advise to wait pros with confirmation...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources