Select second row of a table - 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 2012 Forums

Transact-SQL (2012)

Select second row of a table

Author

Topic

baijuep
Starting Member

15 Posts

Posted - 2014-09-06 : 12:01:51

i have this query to count people who are in different deployment



SELECT Trade.Trade, Trade.Auth, 
(select count(tradeno) from Member where trade=Trade.Trade) AS Held,
(select count(tradeno) from Member where trade=Trade.Trade and Status='Present') AS Present,
(select count(tradeno) from Member where trade=Trade.Trade and Status='KL') AS KL,
(select count(tradeno) from Member where trade=Trade.Trade and Status='HL') AS HL,
(select count(tradeno) from Member where trade=Trade.Trade and Status='SL') AS SL,
(select count(tradeno) from Member where trade=Trade.Trade and Status='TTT') AS TTT,
(select count(tradeno) from Member where trade=Trade.Trade and Status='COURSE') AS COURSE,
(select count(tradeno) from Member where trade=Trade.Trade and Status='UD') AS UD,
(select count(tradeno) from Member where trade=Trade.Trade and Status='LAW') AS LAW,
(select count(tradeno) from Member where trade=Trade.Trade and Status='MAL') AS MAL
FROM Trade ORDER BY id"

the above query works for me fine. now there is a table named PL with field PL

PL

KM

KT

KM

HG

TG

HG

i want to make six different queries for each PL. add a clause in above query

1. to select top 1 PL and also add where clause in the above query so that people employed in KM only gets counted and displayed

2. to select second row of PL and also add where clause in the above query so that people employed in KT only gets counted and displayed

as also for all other PL. please help me

i found a code as below . how can i incorporate this with my code


WITH Rows AS (
		SELECT (ROW_NUMBER() OVER (ORDER BY platoon.pl)) as row,*
		FROM platoon)
	SELECT * FROM Rows WHERE row = 2

baijuep

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-09-06 : 15:04:47

[code]SELECT t.Trade,
t.Auth,
m.Held,
m.Present,
m.KL,
m.HL,
m.SL,
m.TTT,
m.COURSE,
m.UD,
m.LAW,
m.MAL
FROM dbo.Trade AS t
OUTER APPLY (
SELECT COUNT(*) AS Held,
COUNT(CASE WHEN m.[Status] = 'Present' THEN t.TradeNo ELSE NULL END) AS Present,
COUNT(CASE WHEN m.[Status] = 'KL' THEN t.TradeNo ELSE NULL END) AS KL,
COUNT(CASE WHEN m.[Status] = 'HL' THEN t.TradeNo ELSE NULL END) AS HL,
COUNT(CASE WHEN m.[Status] = 'SL' THEN t.TradeNo ELSE NULL END) AS SL,
COUNT(CASE WHEN m.[Status] = 'TTT' THEN t.TradeNo ELSE NULL END) AS TTT,
COUNT(CASE WHEN m.[Status] = 'COURSE' THEN t.TradeNo ELSE NULL END) AS COURSE,
COUNT(CASE WHEN m.[Status] = 'UD' THEN t.TradeNo ELSE NULL END) AS UD,
COUNT(CASE WHEN m.[Status] = 'LAW' THEN t.TradeNo ELSE NULL END) AS LAW,
COUNT(CASE WHEN m.[Status] = 'MAL' THEN t.TradeNo ELSE NULL END) AS MAL
FROM dbo.Member AS m
WHERE m.Trade = t.TRade
) AS m
ORDER BY t.Id;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

baijuep
Starting Member

15 Posts

Posted - 2014-09-06 : 23:21:55

Sir
The first query which i had given is correct and it works for me. Now i want to add one more clause that PL.PL and that PL should be dynamically selected and not manually added in query. The logic should be like this

Above query + where PL.PL= second row of table PL that is the result required for me

First problem is : I was not able to add the clause WHERE PL.PL=' '
Second Problem is : If i had solved my first one the PL.PL must be automatically selected as i had given a sample query to select the second row from table PL.
Then it is solved

baijuep

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-09-07 : 05:35:22

Yes, but the performance of your query is really bad. The performance of my suggestion is great.
This will be even faster

SELECT		t.Trade,
		t.Auth, 
		m.Held,
		m.Present,
		m.KL,
		m.HL,
		m.SL,
		m.TTT,
		m.COURSE,
		m.UD,
		m.LAW,
		m.MAL
FROM		dbo.Trade AS t
LEFT JOIN	(
			SELECT		Trade,
					COUNT(*) AS Held,
					COUNT(CASE WHEN [Status] = 'Present' THEN TradeNo ELSE NULL END) AS Present,
					COUNT(CASE WHEN [Status] = 'KL' THEN TradeNo ELSE NULL END) AS KL,
					COUNT(CASE WHEN [Status] = 'HL' THEN TradeNo ELSE NULL END) AS HL,
					COUNT(CASE WHEN [Status] = 'SL' THEN TradeNo ELSE NULL END) AS SL,
					COUNT(CASE WHEN [Status] = 'TTT' THEN TradeNo ELSE NULL END) AS TTT,
					COUNT(CASE WHEN [Status] = 'COURSE' THEN TradeNo ELSE NULL END) AS COURSE,
					COUNT(CASE WHEN [Status] = 'UD' THEN TradeNo ELSE NULL END) AS UD,
					COUNT(CASE WHEN [Status] = 'LAW' THEN TradeNo ELSE NULL END) AS LAW,
					COUNT(CASE WHEN [Status] = 'MAL' THEN TradeNo ELSE NULL END) AS MAL
			FROM		dbo.Member
			GROUP BY	Trade
		) AS m ON m.Trade = t.Trade
ORDER BY	t.Id;

First learn how to code wise and then we can talk about implementing your requirements the correct way.
Otherwise, when coding bad, the implementations will be even worse.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-09-07 : 05:38:58

Also, how is Platoon table related to Member and Trade?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

baijuep
Starting Member

15 Posts

Posted - 2014-09-07 : 12:05:29

Sir, I really appreciate your effort made the query is really faster than which i had made. Only problem is in my code if the person is not in table member then it shows 0 and in your code it shows NULL. image att for ready ref.
http://imgur.com/Jn46OwV

as well as relation with Table named PL (not platoon) and Trade they are separate table with no relation with member

WITH Rows AS (
SELECT (ROW_NUMBER() OVER (ORDER BY PL.pl)) as row,*
FROM PL)
SELECT * FROM Rows WHERE row = 2

when we add a new person we select the trade from table trade and select the PL from table PL then this data is entered into member table

quote:
Originally posted by SwePeso

Also, how is Platoon table related to Member and Trade?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

baijuep

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources