SQLTeam.com | Weblogs | Forums

Need Highest 2 values from a column


#1

Hello, I need some assistance with a query. I have a table with a column called 'auditkey'. It is all numeric. I need to grab the 2 highest numbers of this column. My query is simple and looks like this:

select auditkey from txtbmast
where mtttaxcode = 'MyTaxCode'

Thanks in advance.


#2
select TOP(2) auditkey from txtbmast
where mtttaxcode = 'MyTaxCode'
ORDER BY auditkey DESC

#3

Very nice. Worked perfectly. Now I need to incorporate more columns in this query. Here is what I have:

select TOP(2) t.auditkey, t.mtttaxcode, t.MttTaxPercentOverBase, t.mttNonResidentTaxRate, t.mttAmtOverWageBase,
t.MttDateTimeChanged, t.mttIfWageBaseIsOver, t.MttIfWageBaseIsOver, c.mtcReciprocityLimit
from txtbmast t join txcdmast c
on t.mtttaxcode = c.mtctaxcode
where mtttaxcode in

(
'MI001','MI002','MI007','MI008','MI013','MI015','MI016','MI019','MI020','MI026','MI027','MI028','MI034','MI037','NMWCEE'
,'NMWCER','NY001','NY006','PA100018','PA102092','PA103015','PA112826','PA113485','PA113628','PA117759','OR001','ORWCEE','WALIEE'
,'WALIER','WASEAER' ,'PA100045','PA108094','PA111185','NMWCEE','NMWCER','PA104041','WALIEE','WALIER','IN174'
,'MD011','MD016','OH1005','OH1034','OH1055','OH1102','OH1172','OH1229','OH1278','OH1445','OH1541'
,'OH1762','OH1794','OH181','OH1844','PA100292'

)

ORDER BY t.auditkey DESC


I need to pull the 2 highest auditkey's for each of the values in the where clause. Right now if I run it, it will only pull in 2 records.


#4

This perhaps?

SELECT	*
FROM
(
	SELECT	[T_RowNumber] = ROW_NUMBER()
			OVER
			(
				PARTITION BY mtttaxcode 
				-- MAKE SURE: that Order By includes unique column(s)
				--	e.g. Primary Key for ALL JOINed tables
				--	so that the TOP 2 part is repeatable
				ORDER BY mtttaxcode, auditkey DESC
			),
		X.*
	FROM
	(
		SELECT	t.auditkey, t.mtttaxcode, t.MttTaxPercentOverBase, t.mttNonResidentTaxRate, 
			t.mttAmtOverWageBase, t.MttDateTimeChanged, t.mttIfWageBaseIsOver, 
			t.MttIfWageBaseIsOver, c.mtcReciprocityLimit 
		FROM	txtbmast t 
			JOIN txcdmast c 
				 ON t.mtttaxcode = c.mtctaxcode
		WHERE	mtttaxcode in
			(
				'MI001','MI002','MI007','MI008','MI013','MI015','MI016','MI019','MI020','MI026','MI027','MI028','MI034','MI037','NMWCEE'
				,'NMWCER','NY001','NY006','PA100018','PA102092','PA103015','PA112826','PA113485','PA113628','PA117759','OR001','ORWCEE','WALIEE'
				,'WALIER','WASEAER' ,'PA100045','PA108094','PA111185','NMWCEE','NMWCER','PA104041','WALIEE','WALIER','IN174' 
				,'MD011','MD016','OH1005','OH1034','OH1055','OH1102','OH1172','OH1229','OH1278','OH1445','OH1541'
				,'OH1762','OH1794','OH181','OH1844','PA100292'
			)
	) AS X
) AS T
WHERE	T_RowNumber <= 2
ORDER BY mtttaxcode, T_RowNumber

#5

Hi Kristen, I may or may not be doing this correctly. I pasted the script in and ran it, but got 2 errors:

Msg 8156, Level 16, State 1, Line 31
The column 'MttIfWageBaseIsOver' was specified multiple times for 'X'.
Msg 8156, Level 16, State 1, Line 32
The column 'MttIfWageBaseIsOver' was specified multiple times for 'T'.

Am I missing a step?


#6

Remove one of the t.mttIfWageBaseIsOver from this

...
SELECT	t.auditkey, t.mtttaxcode, t.MttTaxPercentOverBase, t.mttNonResidentTaxRate, 
		t.mttAmtOverWageBase, t.MttDateTimeChanged, -- >>>> t.mttIfWageBaseIsOver, 
		t.MttIfWageBaseIsOver, c.mtcReciprocityLimit

#7

Nice work. Appreciate your support.