Search code examples
sqlvb.nettableadapter

Most frequent column value in a database table vb.net

i have a database table called parts 

ID|Name|quanity
1 |p1   |20
2 |p2   |60
3 |p3   |200
4 |p1   |50
5 |p1   |40
6 |p3   |70

i want to retrieve the most frequent part name and in our example its p1 because it occurred 3 times and then i want to retrieve the name of part which has largest sum of quantity which is p3 with quantity of 270

i tried this query in my tableadapter 

SELECT  [Name] FROM [Spareparts] GROUP BY  [Name]   ORDER BY [Name]  DESC

but it doesn't work

Solution

  • Well . . . you need to order by the frequency, not the name:

    SELECT [Name]
FROM [Spareparts]
GROUP BY  [Name]  
ORDER BY COUNT(*) DESC

    If you want only one row, then add TOP (1) or FETCH FIRST 1 ROW ONLY or LIMIT 1, depending on your database.

    EDIT:

    Does this work if you include the count in the SELECT?

    SELECT [Name], COUNT(*) as cnt
FROM [Spareparts]
GROUP BY  [Name]  
ORDER BY cnt DESC