query to select the minimum value with a condition

Question

0.00/5 (No votes)

See more:

look my table

table name: special_ad

id video_id play_mode play_count
1 15 both 2
2 16 after 2
3 17 before 1
4 18 both 2
5 19 after 2

now i want to select a play_mode note equal to 'before' and playcount is minimum.

i'm using the following query but it returns a null value
Select video_id,playmode,play_count from special_ad where play_count=(select min(play_count) from special_ad where playmode<>'before')

it will retrieve if the play_count of 'before' should be higher than others
pls suggest a better way

Posted 7-Jul-13 19:56pm

Tricube

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ErBhati · Answer 1 · 2013-07-07T20:27:00

Solution 1

your Query running well

SQL

Select video_id,play_mode,play_count from video_tbl 
where play_count=(select min(play_count)from video_tbl where play_mode !='before'  group by play_count  )

the output of this query is

SQL

video_id  play_mode  play_count 
15	    both	 2
16	    after	 2
18	    both	 2
19	    after	 2

Posted 7-Jul-13 20:27pm

ErBhati

Updated 7-Jul-13 20:29pm

v3

Comments

Tricube 8-Jul-13 2:53am

shows syntax error

ErBhati 10-Jul-13 2:32am

check column name and table name as per your table in database