Printable Version of Topic
914World.com _ 914World Garage _ OT: need SQL help
Posted by: SirAndy Jul 22 2004, 12:30 PM
ok, here's the problem:
let's say i have a table with 2 rows like the example below. in the second row, i have a integer status value (iStatus). i need a query that allows me to find the value that occurs the most in the table, in the example below, that would be "3". i'm not interested in how many 3s there are, all i need is to find out which iStatus value shows up the most in the table ...
Attached image(s)
Posted by: lapuwali Jul 22 2004, 12:36 PM
Oracle, MySQL, SQLServer, DB2, etc? It will make a difference. There is no single "SQL".
Just off the top of by non-guru head, I'd say you're looking at using MAX(COUNT(*)), probably with a sub-select, but I can't give you exact syntax w/o knowing which SQL, and I only know MySQL and Oracle (and I don't know Oracle well).
Posted by: SirAndy Jul 22 2004, 12:40 PM
QUOTE(lapuwali @ Jul 22 2004, 11:36 AM)
Oracle, MySQL, SQLServer, DB2, etc? It will make a difference. There is no single "SQL".
Just off the top of by non-guru head, I'd say you're looking at using MAX(COUNT(*)), probably with a sub-select, but I can't give you exact syntax w/o knowing which SQL, and I only know MySQL and Oracle (and I don't know Oracle well).
count(*) won't work. as i said, i don't need to know how MANY 3s there are ...
just ANSI SQL, the query should be easy enough to run anywhere, i'm hoping i don't need some crazy syntax for what i want.
Andy
Posted by: Cloudbuster Jul 22 2004, 01:18 PM
select top 1 count(iStatus) as StatCount, sDescription from {tablename} group by sDescription
order by StatCount
You'll need to replace "{tablename}" with the actual name
Posted by: SirAndy Jul 22 2004, 01:28 PM
QUOTE(Cloudbuster @ Jul 22 2004, 12:18 PM)
select top 1 count(iStatus) as StatCount, sDescription from {tablename} group by sDescription
order by StatCount
nope, that just returns the first row in the table ...
Attached image(s)
Posted by: Deluxe247 Jul 22 2004, 01:28 PM
To get the mode do this:
SELECT TOP 1 COUNT(*) AS 'Number of repeats', rowName AS Number
FROM dbo.tblName
GROUP BY rowName
ORDER BY COUNT(*) DESC
It will return how many times the number occured and which number. You can then pull whatever you want from the result; the actual number.
Posted by: SirAndy Jul 22 2004, 01:38 PM
QUOTE(Deluxe247 @ Jul 22 2004, 12:28 PM)
To get the mode do this:
SELECT TOP 1 COUNT(*) AS 'Number of repeats', rowName AS Number
FROM dbo.tblName
GROUP BY rowName
ORDER BY COUNT(*) DESC
It will return how many times the number occured and which number. You can then pull whatever you want from the result; the actual number.
perfect! that worked ...
thank you very much, you just saved me a ton of work!
next
will be on me,
Andy
Posted by: vortrex Jul 22 2004, 01:41 PM
make a text file.
cat filename | cut -f2 -d" " | sort -rn | uniq | head -1
Posted by: SirAndy Jul 22 2004, 02:02 PM
QUOTE(vortrex @ Jul 22 2004, 12:41 PM)
make a text file.
cat filename | cut -f2 -d" " | sort -rn | uniq | head -1
it'll be part of a humongous script that'll migrate data from one DB into another.
no time and patience to manually fiddle with text files.
i'm hoping to run this overnight and the new and oh so much better database will be ready to go in the morning ...
Andy
Powered by Invision Power Board (http://www.invisionboard.com)
© Invision Power Services (http://www.invisionpower.com)