I hope to get max 5 values from SQL server

Question

1.00/5 (2 votes)

See more:

Sql server
I have 50 columns with 1200 rows ,
I need get Max 5 values with which columns got ,
In Sql server

What I have tried:

SQL

SELECT  BINANCE.symbol,BINANCE.bidPrice as BINANCE ,MEXC.bidPrice as MEXC, WAZIRX.bidPrice as WAZIRX,
 IIF(BINANCE.bidPrice >  MEXC.bidPrice, 'BINANCE', IIF (MEXC.bidPrice >  WAZIRX.bidPrice, 'MEXC' , 'WAZIRX')) AS GetMax 
FROM BINANCE,MEXC ,WAZIRX;

Posted 10-Aug-23 9:09am

Member 16069108

Updated 10-Aug-23 23:56pm

Andre Oosthuizen

v2

Add a Solution

2 solutions

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)

Mike Hankey · Answer 1 · 2023-08-10T13:49:00

Solution 1

According to this article; SQL SELECT TOP, LIMIT, FETCH FIRST ROWS ONLY, ROWNUM[^]

The way to do it is;

SQL

	SELECT TOP 5 BINANCE.symbol,BINANCE.bidPrice as BINANCE ,MEXC.bidPrice as MEXC, WAZIRX.bidPrice as WAZIRX,
 IIF(BINANCE.bidPrice >  MEXC.bidPrice, 'BINANCE', IIF (MEXC.bidPrice >  WAZIRX.bidPrice, 'MEXC' , 'WAZIRX')) AS GetMax 
FROM BINANCE,MEXC ,WAZIRX;

Posted 10-Aug-23 13:49pm

Mike Hankey

Comments

Member 16069108 11-Aug-23 9:59am

that is top 5 from column, but i need 5 values from rows ,
i have 50 columns with 1200 rows

Andre Oosthuizen · Answer 2 · 2023-08-10T23:56:00

Solution 2

As addition to Mike's solution, I have used the CHARINDEX method which checks for a comma - MS Learn | CHARINDEX (Transact-SQL)[^]

SQL

SELECT TOP 5
    BINANCE.symbol,
    BINANCE.bidPrice AS BINANCE,
    MEXC.bidPrice AS MEXC,
    WAZIRX.bidPrice AS WAZIRX,
    IIF(BINANCE.bidPrice > MEXC.bidPrice, 'BINANCE',
        IIF(MEXC.bidPrice > WAZIRX.bidPrice, 'MEXC', 'WAZIRX')) AS GetMax 
FROM
    BINANCE,
    MEXC,
    WAZIRX
WHERE
    CHARINDEX(',', BINANCE.bidPrice) > 0
    OR CHARINDEX(',', MEXC.bidPrice) > 0
    OR CHARINDEX(',', WAZIRX.bidPrice) > 0
ORDER BY
    GetMax;

Posted 10-Aug-23 23:56pm

Andre Oosthuizen

Updated 10-Aug-23 23:57pm

v2

Comments

Member 16069108 11-Aug-23 10:02am

that is top 5 from column, but i need 5 values from rows ,
i have 50 columns with 1200 rows

Andre Oosthuizen 11-Aug-23 10:25am

Ok, and how will you determine which 5 to choose if there are more than 5 returned rows?

Member 16069108 11-Aug-23 17:50pm

ID Name BINANCE MEXC WAZIRX D4 D5 D6 D7 D8 D9 D10 D11 Max Values Name of Values
1 ETHBTC 50.0 20.0 15.0 22.0 5.0 30.0 50.0 8.0 1.0 10.0 100.0 100 50 50 30 22 D11 BINANCE D7 D6 D4
2 LTCBTC 7.0 20.0 15.0 22.0 5.0 6.0 7.0 8.0 9.0 10.0 11.0 22 20 15 11 10
3 BNBBTC 200.0 20.0 15.0 22.0 5.0 6.0 7.0 8.0 9.0 10.0 11.0 200 22 20 15 11
4 NEOBTC 7.0 20.0 15.0 22.0 5.0 6.0 7.0 8.0 9.0 10.0 11.0 22 20 15 11 10

Member 16069108 11-Aug-23 17:53pm

i do in excel file but i can't in sql

Andre Oosthuizen 12-Aug-23 4:18am

Help me if I am wrong, you need 5 items (the first 5) returned PER ROW and not column? I don't see any commas in your 1-4 example above. You need to explain better please as it does not make sense on what you need.

If it is what you need, it is going to be very complex as you need to break down each value before you can move on to the next to catch the comma values.

Maybe show me the data you are working with as I believe the data you are showing above is the excel returned data.

Member 16069108 13-Aug-23 12:58pm

That is code for excel
to get value
=IFERROR(LARGE($BA3:$BX3;COLUMN(A$3));"")
to get Name of value
=IFNA(IF(BZ3="";"";OFFSET(INDEX($BA3:$BX3;1;MATCH(BZ3;$BA3:$BX3;0));-$A3;0));"")
in sql
i will try to Convert rows to columns will get answer