CODEWITHSUNDEEP
sqlms-access
que
que

Reputation: 121

SQL for Middle Value Rather than MIN/MAX or FIRST/LAST

Is there a SQL function to return the middle value of three?

For example, assume I have a table with people who have three cars, sorted alphabetically by AutoMaker.

John: Ford
John: Honda
John: VW

then

MIN(AutoMaker) returns Ford.
MAX(AutoMaker) returns VW.

Is there a similar SQL function that will return Honda?

I am working with MS Access and Oracle.

Thank you.

Upvotes: 0

Views: 3763

Answers (2)

Petter Friberg
Petter Friberg

Reputation: 21710

The query could be something like this

select row_id, Field1   
FROM tbl 
where row_id  = (select cInt(count(Field1)/2) from tbl)

The problem in access is that you do not have a row_number you would need to add a row_id to the table and then populate row_id 1,2,3,4 (ordered on Field1)

Upvotes: 0

Mihai Ovidiu Drăgoi
Mihai Ovidiu Drăgoi

Reputation: 1317

Short answer: No. It's too specific.

Longer answer: It's too specific. Hence, the "middle" in what you said is actually the second record. But if you had 5 records, it would be the third, and so on. If you need that in practice, just assign a row number to each row (Oracle, Access) and then select the ((n+1)/2)nd row (WHERE row_number = (n+1)/2).

PS - which is the middle row if you have 4 rows? :)

Upvotes: 2

Related Questions