Post reply

select the most appearance

  • August 18, 2009 at 2:23 am

    #203854

    Dear all,

    i have a table with one column, im saving the user selected cars (via my application) in this table.

    How can i select the value that appearances most in this table.

    for example if i have the following values:

    mercedes

    bmw

    mercedes

    toyota

    toyota

    mercedes

    in this case, the car 'mercedes' appearances most then other cars. how can i do a select to get this?

    (sorry for my english).

    Thanks in advance.

  • August 18, 2009 at 3:00 am

    #1040682

    create table #cars(

    carname varchar(20)

    )

    go

    insert into #cars values('ford')

    insert into #cars values('ford')

    insert into #cars values('ford')

    insert into #cars values('honda')

    insert into #cars values('honda')

    go

    Select top 1 carname,count(*)

    from #cars

    group by carname

    order by 2 desc


    Clear Sky SQL
    My Blog[/url]

  • August 18, 2009 at 3:02 am

    #1040685

    SELECT TOP 1 Manufacturer

    FROM CarManufacturer

    GROUP BY Manufacturer

    ORDER BY COUNT(1) DESC

  • August 18, 2009 at 3:23 am

    #1040694

    hi,

    nice coding by Dave and clive

    suppose the count occure same for 2 cars then?

    like

    insert into #cars values('ford')

    insert into #cars values('ford')

    insert into #cars values('ford')

    insert into #cars values('ford')

    insert into #cars values('honda')

    insert into #cars values('honda')

    insert into #cars values('AA')

    insert into #cars values('AA')

    insert into #cars values('AA')

    insert into #cars values('AA')

  • August 18, 2009 at 8:14 am

    #1040900

    Yes, good question from Arun. What then? 😉

  • August 18, 2009 at 8:30 am

    #1040924

    Should you not be telling us what you'd expect to see?

    😛

  • August 18, 2009 at 8:49 am

    #1040935

    SELECT TOP 1 WITH TIES Manufacturer, COUNT(*) FROM...

    Dave

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply