{"id":107,"date":"2014-05-07T14:16:00","date_gmt":"2014-05-07T13:16:00","guid":{"rendered":"https:\/\/debuggersspace.com\/index.php\/2014\/05\/07\/second-biggest-number-using-sql-query\/"},"modified":"2014-05-07T14:16:00","modified_gmt":"2014-05-07T13:16:00","slug":"second-biggest-number-using-sql-query","status":"publish","type":"post","link":"https:\/\/debuggersspace.com\/index.php\/2014\/05\/07\/second-biggest-number-using-sql-query\/","title":{"rendered":"Second biggest number using sql query?"},"content":{"rendered":"
\n
\n \t<\/i> Read Time:<\/span>51 Second <\/div>\n\n <\/div>
\n
\nI have a task to get the second biggest number using sql query. I know that we can get the biggest number using SQL very easily: select Max(NumColumn) from TableName
\n\n\nFor example, I have the following records:
\nNumColumn<\/div>\n
\n     1<\/div>\n
\n     2<\/div>\n
\n     33<\/div>\n
\n     44<\/div>\n
\n     100<\/div>\n
\n     110<\/div>\n
\n     180<\/div>\n
\nIf I use \"select Max(NumColumn) from aTable\", I will get the number: 180.<\/div>\n
\nBut I need to use SQL to the second biggest number: 110.<\/div>\n<\/span><\/code><\/pre>\n
Answer1:<\/u><\/b><\/span><\/pre>\n
select<\/span> col<\/span><\/span><\/pre>\n
from<\/span> <\/span>(<\/span>\n    <\/span>select<\/span> ROW_NUMBER<\/span>()<\/span> <\/span>over<\/span> <\/span>(<\/span>order<\/span> <\/span>by<\/span> col <\/span>asc<\/span>)<\/span> <\/span>as<\/span> <\/span>'rowNum'<\/span>,<\/span> col\n    <\/span>from<\/span> <\/span>[<\/span>table<\/span>]<\/span> <\/span>)<\/span> withRowNum \n<\/span>where<\/span> rowNum <\/span>=<\/span> <\/span>2<\/span><\/span><\/code><\/pre>\n
Answer2:<\/u><\/b><\/span><\/pre>\n
SELECT<\/span> <\/span>MAX<\/span>(<\/span>NumColumn<\/span>)<\/span> <\/span>FROM<\/span> aTable <\/span>WHERE<\/span> NumColum <\/span><<\/span> <\/span>(<\/span>SELECT<\/span> <\/span>MAX<\/span>(<\/span>NumColumn<\/span>)<\/span> <\/span>FROM<\/span> aTable<\/span>)<\/span><\/span><\/pre>\n
Answer3:<\/u><\/b><\/span><\/pre>\n
select<\/span> min<\/span>(<\/span>NumColumn<\/span>)<\/span> <\/span>from<\/span> TableName <\/span>where<\/span> <\/span>NumColumn<\/span> <\/span>in<\/span> <\/span>(<\/span>select<\/span> <\/span>TOP<\/span> <\/span>2<\/span> <\/span>(<\/span>NumColumn<\/span>)<\/span> <\/span>from<\/span> TableName <\/span>order<\/span> <\/span>by<\/span> <\/span>NumColumn<\/span> <\/span>desc<\/span>)<\/span><\/span><\/pre>\n
<\/span>
\n<\/span><\/code>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
I have a task to get the second biggest number using sql query. I know that we can get the biggest number using SQL very easily: select Max(NumColumn) from TableName For example, I have the following records: NumColumn      1      2      33      44      100      110      180 If I use “select Max(NumColumn) from […]<\/p>\n","protected":false},"author":43,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_uag_custom_page_level_css":"","footnotes":""},"categories":[27],"tags":[],"class_list":["post-107","post","type-post","status-publish","format-standard","hentry","category-sql"],"uagb_featured_image_src":{"full":false,"thumbnail":false,"medium":false,"medium_large":false,"large":false,"1536x1536":false,"2048x2048":false},"uagb_author_info":{"display_name":"Himanshu Namdeo","author_link":"https:\/\/debuggersspace.com\/author\/admin\/"},"uagb_comment_info":0,"uagb_excerpt":"I have a task to get the second biggest number using sql query. I know that we can get the biggest number using SQL very easily: select Max(NumColumn) from TableName For example, I have the following records: NumColumn      1      2      33      44      100      110      180 If I use \"select Max(NumColumn) from…","_links":{"self":[{"href":"https:\/\/debuggersspace.com\/index.php\/wp-json\/wp\/v2\/posts\/107"}],"collection":[{"href":"https:\/\/debuggersspace.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/debuggersspace.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/debuggersspace.com\/index.php\/wp-json\/wp\/v2\/users\/43"}],"replies":[{"embeddable":true,"href":"https:\/\/debuggersspace.com\/index.php\/wp-json\/wp\/v2\/comments?post=107"}],"version-history":[{"count":0,"href":"https:\/\/debuggersspace.com\/index.php\/wp-json\/wp\/v2\/posts\/107\/revisions"}],"wp:attachment":[{"href":"https:\/\/debuggersspace.com\/index.php\/wp-json\/wp\/v2\/media?parent=107"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/debuggersspace.com\/index.php\/wp-json\/wp\/v2\/categories?post=107"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/debuggersspace.com\/index.php\/wp-json\/wp\/v2\/tags?post=107"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}