CODEWITHSUNDEEP
sqlsqlite
WhatAName
WhatAName

Reputation: 53

find MIN without using min()

I am trying to find student who has min score which will be the result of the below query. However, I was asked to write the query without using MIN(). Spent several hours but I can't find the alternative solution :'(. 

select s.sname
from student s
where s.score = 
    (select min(s2.score) 
    from score s2)

Upvotes: 0

Views: 6721

Answers (5)

Viswas Menon
Viswas Menon

Reputation: 310

One way of doing it would be to Order the results in Ascending order and take the first row. But if you are looking at a more generic solution as a student will have more than one mark associated with him, So you need to find the total marks for each student and then find the student with the least total.

This is the first scenario, A student only has one row in the table.

CREATE TABLE Student
(
SLNO INT,
MARKS FLOAT,
NAME NVARCHAR(MAX)
)

INSERT INTO Student VALUES(1, 80, 't1')
INSERT INTO Student VALUES(2, 90, 't2')
INSERT INTO Student VALUES(3, 76, 't3')
INSERT INTO Student VALUES(4, 98, 't4')
INSERT INTO Student VALUES(5, 55, 't5')

SELECT * From Student ORDER BY MARKS ASC

The second scenario as specified above is, He has multiple rows in the table, So we insert two more rows into the table for existing users.

Then we select the users by taking the sum of their marks grouping the results by name and then ordering the results by their total

INSERT INTO Student VALUES(6, 55, 't1')
INSERT INTO Student VALUES(6, 90, 't5')


SELECT SUM(MARKS) AS TOTAL, NAME FROM Student 
GROUP BY NAME
ORDER BY TOTAL

Hope the above is what you are looking for.

Upvotes: 1

JL Peyret
JL Peyret

Reputation: 12164

create table student (name varchar(10), score int);
insert into student (name, score) values('joe', 30);
insert into student (name, score) values('jim', 88);
insert into student (name, score) values('jack', 22);
insert into student (name, score) values('jimbo', 15);
insert into student (name, score) values('jo bob',15);

/* folks with lowest score */
select name, score from student where not exists(select 1 from student s where s.score < student.score);

/* the actual lowest score */
select distinct score from student 
where not exists(select 1 from student s where s.score < student.score);

Note that not exists can be brutally inefficient, but it'll do the job on a small set.

Upvotes: 1

user1864610
user1864610

Reputation:

Here's a possible alternative to the JOIN approach:

select sname from student where score in 
   (select score from student order by score asc limit 1)

Upvotes: 1

Swap
Swap

Reputation: 3

You can try stored procedure to find student with minimum score.

Upvotes: -2

radar
radar

Reputation: 13425

This is one way, which will work even if two students have same lowest score.

SELECT distinct s1.sname
FROM student s1
  LEFT JOIN student s2
    ON s2.score < s1.score 
WHERE s2.score IS NULL

The below is the method using limit, which will return lowest score student, but only one of them if multiple of them have same score.

select sname
from student
order by score asc
limit 1

Upvotes: 3

Related Questions