Select second row of a table

Question

Select second row of a table

baijuep

Valued Member

Points: 59
More actions
September 6, 2014 at 10:15 am

#147054

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 googled and got this code it works how can i incorporate to my code above
WITH Rows AS (
SELECT (ROW_NUMBER() OVER (ORDER BY platoon.pl)) as row,*
FROM platoon)
SELECT * FROM Rows WHERE row = 2

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

whats the relationship between "Trade" table and "Platoon" table?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

baijuep Valued Member Points: 59 More actions · Answer 2

sir there is no relationship between these two tables

when a new entry is made for a person . Then the PL, trade are selected by the operator in the add interface.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

I don't understand your problem. What's PL, KM, KT? How do you define the first and the second row? There's no default order on SQL.

Your query seems inefficient. You could change it to this alternatives. The first is almost guaranteed to return the correct results (I can't test it without sample data) and the second one should be more efficient.

WITH AggMember AS(

select trade,

COUNT(tradeno) AS Held,

COUNT(CASE WHEN Status='Present' THEN tradeno END) AS Present,

COUNT(CASE WHEN Status='KL' THEN tradeno END) AS KL,

COUNT(CASE WHEN Status='HL' THEN tradeno END) AS HL,

COUNT(CASE WHEN Status='SL' THEN tradeno END) AS SL,

COUNT(CASE WHEN Status='TTT' THEN tradeno END) AS TTT,

COUNT(CASE WHEN Status='COURSE' THEN tradeno END) AS COURSE,

COUNT(CASE WHEN Status='UD' THEN tradeno END) AS UD,

COUNT(CASE WHEN Status='LAW' THEN tradeno END) AS LAW,

COUNT(CASE WHEN Status='MAL' THEN tradeno END) AS MAL

from Member

GROUP BY trade

)

SELECT Trade.Trade,

Trade.Auth,

m.Held,

m.Present,

m.KL,

m.HL,

m.SL,

m.TTT,

m.COURSE,

m.UD,

m.LAW,

m.MAL

FROM Trade t

JOIN AggMember m ON t.trade = m.trade;

SELECT t.Trade,

t.Auth,

COUNT(tradeno) AS Held,

COUNT(CASE WHEN Status='Present' THEN tradeno END) AS Present,

COUNT(CASE WHEN Status='KL' THEN tradeno END) AS KL,

COUNT(CASE WHEN Status='HL' THEN tradeno END) AS HL,

COUNT(CASE WHEN Status='SL' THEN tradeno END) AS SL,

COUNT(CASE WHEN Status='TTT' THEN tradeno END) AS TTT,

COUNT(CASE WHEN Status='COURSE' THEN tradeno END) AS COURSE,

COUNT(CASE WHEN Status='UD' THEN tradeno END) AS UD,

COUNT(CASE WHEN Status='LAW' THEN tradeno END) AS LAW,

COUNT(CASE WHEN Status='MAL' THEN tradeno END) AS MAL

FROM Trade t

JOIN Member m ON t.trade = m.trade;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

baijuep Valued Member Points: 59 More actions · Answer 4

Sir, i tried the second query and it gives following error

Msg 8120, Level 16, State 1, Line 1

Column 'Trade.Trade' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 5

baijuep (9/7/2014)
Sir, i tried the second query and it gives following error
Msg 8120, Level 16, State 1, Line 1
Column 'Trade.Trade' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I'm sure that if you know the basics of SQL, you should be able to add the missing GROUP BY clause that I missed. 😉

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2