2nd lowest with tie

I am working with 3 tables: STUDENTS, GRADES, COURSES

Students GRADES COURSES

Jean 88 Algebra I

Rachel 55 Chemistry

Charlie 75 History

Bud 90 Algebra I

JD 62 Chemistry

Alton 96 History

Jean 81 History

Rachel 40 Algebra I

Jean 79 Chemistry

Charlie 64 Chemistry

Alton 83 Algebra I

Bud 70 Chemistry

I need to find only the second lowest grade among the scores where the student did not take enrolled for History.

thank you

Any comment is welcome

3 tables or 3 columns ?|||declare @.table table
(
Students varchar(10),
GRADES int,
COURSES varchar(10)
)
insert into @.table
select 'Jean', 88, 'Algebra I' union all
select 'Rachel', 55, 'Chemistry' union all
select 'Charlie', 75, 'History' union all
select 'Bud', 90, 'Algebra I' union all
select 'JD', 62, 'Chemistry' union all
select 'Alton', 96, 'History' union all
select 'Jean', 81, 'History' union all
select 'Rachel', 40, 'Algebra I' union all
select 'Jean', 79, 'Chemistry' union all
select 'Charlie', 64, 'Chemistry' union all
select 'Alton', 83, 'Algebra I' union all
select 'Bud', 70, 'Chemistry'

select top 1 *
from
(
select top 2 *
from @.table t
where not exists (select * from @.table x where x.COURSES = 'History' and x.Students = t.Students)
order by GRADES
) a
order by GRADES desc|||

I apologize and should have specify the tables structure. You may disregard my previous post. I am working with 3 tables: INVOICE, TELEVISION, INV_LINE_ITEM.

INVOICE Table TELEVISION Table INV_LINE_ITEM Table

InvoiceDt Type InvoiceNbrFK

InvoiceNbrPK Manufacturer SerialNumberFK

EmpIDFK size Quantity

TotalPrice SerialNumberPK LineNbr

I just want to find out only the invoice number that contains the second lowest total price among the invoices and where the type is not HDTV.

I wrote this query here but I am not so sure about it. Help

SELECT top 1 Invoicenbr, CONVERT(CHAR(25), invoicedt)AS 'Invoice Date',

CAST(min(totalprice) as char(10)) as 'Total Price'

FROM invoice

WHERE totalprice >

(SELECT min(totalprice)

FROM invoice

WHERE invoicenbr in

(SELECT invoicenbrFK

FROM inv_line_item

WHERE serialnumberFK not in

(SELECT serialnumber

FROM Television

WHERE serialnumber = serialnumberFK and type< >'hdtv')))

GROUP by totalprice, invoicenbr, invoicedt;;

|||

Am I right in thinking that you need the invoice with the second lowest price which does not include a line for a television of type HDTV.

In which case the variation on K H Tan's example is I believe:

SELECT TOP 1 InvoiceNbr,
CONVERT(CHAR(25), invoicedt)AS 'Invoice Date',
CAST(min(totalprice) as char(10)) as 'Total Price'
FROM (
SELECT TOP 2 *
FROM INVOICE
WHERE InvoiceNbr NOT IN (
SELECT LIN.InvoiceNbr
FROM INV_LINE_ITEM LIN
INNER JOIN TELEVISION TV
ON (LIN.SerialNumber = TV.SerialNumber ) AND
(TV.Type = 'hdtv')
)
ORDER BY TotalPrice
) A
ORDER BY TotalPrice DESC

I did not use the PK and FK suffixes as you did not have them in your sample SQL.

|||mermaid,

can you post the table structure with sample data and the result that you want similar to my 1st post it will be easier for us to help you|||

Hi,

Your example was a very great help. I added a few changes and I got the projection expected. Here is my final query:

SELECT TOP 1 I.InvoiceNbr,
CONVERT(CHAR(25), I.invoicedt)AS 'Invoice Date',
CAST(min(totalprice) as char(10)) as 'Total Price'
FROM (
SELECT TOP 2 *
FROM INVOICE
WHERE InvoiceNbr NOT IN (
SELECT LIN.InvoiceNbr
FROM INV_LINE_ITEM LIN
JOIN TELEVISION TV
ON (LIN.SerialNumber = TV.SerialNumber ) AND
(TV.Type = 'hdtv')
)
ORDER BY TotalPrice
) I
ORDER BY TotalPrice DESC

Thank you so much.

|||

Hi,

I work on the example posted by one of the user and got the right result. Thank you K H and I do appreciate your help.