CODEWITHSUNDEEP
sqlsql-server
Rohit
Rohit

Reputation: 21

SQL query for Most frequent value in the row

My Database looks something similar to the below one.

ID   A1   A2   A3    A4
7   2.31 2.31 2.31  2.32
8   2.32 2.32 2.32  2.32
9   2.31 2.3  2.31  2.31
10  2.32 2.3  2.32  2.31

Now I'm looking for an SQL query which gives the output as below (picks the most frequent value from A1,A2,A3,A4 for each value in the ID and display it in the column A)

ID  A
7   2.31
8   2.32
9   2.31
10  2.32

Can someone please help me on this..

Upvotes: 0

Views: 222

Answers (3)

Ultimater
Ultimater

Reputation: 4738

Since there's only 4 values involved with each row, there's only a need to check for a single matching pair. Because if there's a tie, you can return either of the two correctly.

Exploiting this, you can pull it off using nothing but if/else logic like so:

SELECT ID,
IIF (
  A1 = A2 OR A1 = A3 OR A1 = A4,
  A1,
  IIF (
    A2 = A3 OR A2 = A4,
    A2,
    A3)
) A FROM mytable

Proof

The solution posted by Giorgos Betsos has cleaner, safer looking logic.
But pretty sure my approach is less resource heavy.

Upvotes: 0

Sandeep Kumar
Sandeep Kumar

Reputation: 315

You can try it by using PIVOT, like below:

CREATE TABLE #TABLE1 (
    ID INT, 
    A1 DECIMAL(10,2), 
    A2 DECIMAL(10,2),
    A3 DECIMAL(10,2), 
    A4 DECIMAL(10,2)
)

INSERT INTO #TABLE1 VALUES (7,2.31,2.31,2.31,2.32);
INSERT INTO #TABLE1 VALUES (8,2.32,2.32,2.32,2.32);
INSERT INTO #TABLE1 VALUES (9,2.31,2.3,2.31,2.31);
INSERT INTO #TABLE1 VALUES (10,2.32,2.3,2.32,2.31);
GO

select * from #TABLE1


SELECT ID, A
FROM (
    SELECT 
        ID, A, count(A) as 'RepeatCount',
        rank() over (partition by ID order by ID, count(A) desc) as 'ranked'
    FROM (
        SELECT ID, A1, A2, A3, A4
        FROM #TABLE1
    ) pvt
    UNPIVOT (
        A FOR COL IN (A1, A2, A3, A4)
    )AS unpvt
    group by ID, A
)A
WHERE ranked = 1
ORDER BY ID

drop table #TABLE1

enter image description here

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can try the following query:

SELECT ID, x.A
FROM mytable
CROSS APPLY (
   SELECT TOP 1 t.A
   FROM (VALUES (A1), (A2), (A3), (A4)) AS t(A)
   GROUP BY t.A
   ORDER BY COUNT(*) DESC) AS x

Demo here

Upvotes: 7

Related Questions