MYSQL :: Ascending Order Number For Every Row In Result Set

Query - Top 20 Results In Ascending Alphabetical Order
I have a table which is a list of categories in an online directory.. each time the category is visited in increases the hits column by one..

I want to get the top 20 results (ie. the top twenty most popular categories) and then sort them into a list alphabetically.

I tihnk I have to use a nested select but when I do this I only get the top 20 results sorted by alphabet not by hits..

Here is the table structure:

CODE#
# Table structure for table `category`
#

CREATE TABLE category (
autoID mediumint(9) NOT NULL auto_increment,
title varchar(100) NOT NULL default '',
hits int(11) NOT NULL default '0',
PRIMARY KEY (autoID)
) TYPE=MyISAM;

Get The Row Number From A Result Set
How can I know the row number of a result?

For example i have a query like this:
"SELECT id FROM table WHERE id = 3"

So is there a way to get the row number where id = 3? I tried using 'mysql_fetch_row()' and 'mysql_affected_rows()' but just cant get what i want.

Trying To Count The Number Of Rows In A Result Set After Query
The user fills out this form to sign up to the website, the form checks the database to see if the username has already been taken with the code:

$conn = mysql_connect("localhost:3306", "root", "********")
                                    or die ("Error With Connection");
        echo("connected<br><br>");
        $db_sel = mysql_select_db("game",$conn)
                                    or die ("Error With Database");
        $check = "select * from users where 'username' = '$username'";
        $db_sel = mysql_query($check,$conn)
                or die (mysql_error());

Order Of Result
I have an sql query which ends with "... AND id IN(" and then a list of id's in a specific order. When I run the query it doesnt return the results in the order that the id was specified. Is this normal or is there a way to ensure that the results will be returned in the order given in the "in" part of the sql statement?

Reverse Order Of Result
i need to reverse the order of the resulting table without messing up what gets cutoff by the limit i.e. result must be ordered oldest to newest with things too old left off.

$shouts=mysql_query("SELECT * FROM posts ORDER BY id DESC LIMIT 15 ");

this is being called from php

How To Get Specific Order Of Result Set (with No Ordering Pattern Except The Input)
I have a query here and I'm stumped on how I can get my result set to be properly ordered:

SELECT w_name FROM weapons WHERE w_name IN ('Rainbow Scorchstone','Dai-scimitar','Zaptwig','Eye Of Storms','Grimoire Of Thade','Advisor Broos Gnarled Staff','Advisor Broos Hat','Advisor Broos Necklace')

What I'm trying to do get the result set to be ordered as written in the IN() section. So in this particular query, I want Rainbow Scorchstone to be my first result, Dai-scimitar to be my second, Zaptwig to be my third, and so on and so forth... how would I go about doing this?

12 Digit Order Number Anyone?
Would anyone know the best datatype for a 12 digit order number? It was just a varchar before but I need to perform arithmetic on it now.

Retrieving Order Number
is there a way to retrieve the order number along with the other fields in a select statement ?

for example if I have a query that selects the following results:

ID Value
-----------
1 1345
3 8275
5 2384
7 2345

I want a way to retrieve the following results:

Recno ID Value
--------------------
1 1 1345
2 3 8275
3 5 2384
4 7 2345

VARCHAR Number Order
Ok, I have a VARCHAR field. It 'usually' has numbers.
Im running this sql.

SELECT expreference FROM expenses ORDER BY expreference DESC LIMIT 0,1

This helps me find the next available number.

However, I do allow the user to enter a value, i.e. GHT3Fs. So I don't want to use a decimal, in INT.

The problem is once I get to 10 the order is shifted.
1, 10, 2, 3, 4, 5, 6, 7, 8, 9

to something like that, which doesn't help when finding the next number.

How Can I Determine The Offset Of A Result In An Ordered Result Set?
How can I determine the offset of a result in an ordered result set?

I would like to pass the calculated offset into the limit half of and ordered select statement.

E.g. I have a table that records a id and datetime for captioned photographs. I'd like to show the five photos that were taken after the photo with id=23.

To do that I need to find the offset of photo with id=23 in

select id, datetime, caption from photos order by datetime;

Then I could get the result I want by doing....

select id, datetime, caption from photos order by datetime limit $offset, 5;

I've spent several hours scouring around and found some people with similar problems, but no solutions yet.

ORDER BY WHERE + Include Rest Of Table Or ORDER? Possible?
I need to create some SQL that when run returns all the rows which a column is equal to number that has been specified, but then the query needs to return the rest of the rows in the table. How could this be done? is it even possible? Is their order syntax that could do this better?

Multiple ORDER & SUB ORDER On The Same MySQL Table
I am trying to do multiple ORDERS or SUB ORDERS on the same MySQL table, and I'm loosing my mind trying to fathom the logic and SQL statement to use, I'm no MySQL genius! more a newbie.

*** SEE ATTACHED IMAGE PLEASE

I have tried all sorts of SQL statements, e.g:

SELECT * FROM categories GROUP BY parent ORDER BY order, parent DESC

Nothing seems to work.

I think my only solution is to do a bubble-sort after putting the whole table in a PHP ARRAY ?

I'd also like the menu to act like the + pop-open sub-menu boxes on the Forum left column menu.

How To ORDER BY DESC Properly. It Makes 150 Before 50 If ASC Order!?
I have this list:
50
150
200
250
300
350
400
450
500

If I order it by ASC order then mysql results this:
150
200
250
300
350
400
450
50
500

What the hell ?? How do I order it properly?

How Does MySQL Order Records With No ORDER BY?
If you don't specify an ORDER BY clause, how does MySQL order the records? Is it just the order in which they found in the database?

Selection Order Without Order By!?
I have a table with this structure

trans
-------
t_no (int auto_increment)
t_timestamp (datetime)
t_price (double)
t_vol (int)

I've inserted two record into trans table with same t_timestamp value. When i do a select * from trans it doesn't always return ascending order based on t_no? But sometimes it does?

I would like to know how MySql order selection result wihtout the order by command based on this scenario.

Order By - Order Of Precedence?
I know how to use the order by, but wondered if there is an order of
precedence in using it.

Facts: hcounty is a county that someone puts in a home county in which they live. In the comments they are also allowed to put text, that might contain a county listing also. I want to have the results put the records that have hcounty matches first, then the records that have a county match in the comments field after the first set of records. I have tried all sorts of order by and group by and can't get the result I'm looking for....

Order By - Order Of Precedence?
I know how to use the order by, but wondered if there is an order of
precedence in using it.

Facts: hcounty is a county that someone puts in a home county in which they
live. In the comments they are also
allowed to put text, that might contain a county listing also. I want to
have the results put the records
that have hcounty matches first, then the records that have a county match
in the comments field after the first set of records. I have tried all sorts
of order by and group by and can't get the result I'm looking for. Any hint
or assistance would be greatly appreciated.

$query = "SELECT * FROM contacts WHERE hcounty ='$searchcounty' or comments
like '%$searchcounty%' order by state, hcounty, dtestart ";

Force Order By Order??
I have 6 rows of data... each has a name, eg: c1, c2, mrgs1, totalfte.
they are all under the one column "field". Is there a way I can force their order??

at the mo, I use "ORDER BY field DESC" and its coming out lile

mrgs1, totalfte, c1, c2

I need

totalfte, mrgs1, c1, c2

is it possible to sort like this??

Custom Order For ORDER BY
I'm grabbing six specific records, like so:

$result = mysql_query("SELECT * FROM casestudies WHERE id=1 OR id=2 OR id=3 OR id=4 OR id=5 OR id=6 ORDER BY ___________",$db);

I'd like to post them in a custom order, specifically: 1,3,4,5,6,2. Can't figure out how to do this with ORDER BY, if that's even possible. Anything I'm missing?

ORDER BY Out Of Order - Fixed
I have a query that uses two tables that I want to produce output like the following:

+-------------+-------------+-----------+
| LOCATION | GROSS SALES | NET SALES |
+-------------+-------------+-----------+
| Location 1 | 11,860,735 | 2,907,552 |
| Location 4 | 4,814,029 | 1,077,003 |
| Location 3 | 2,711,795 | 710,804 |
| Location 5 | 2,660,040 | 666,255 |
| Location 2 | 2,049,470 | 563,830 |
| Location 8 | 2,227,730 | 543,220 |
| Location 7 | 1,766,880 | 425,483 |
| Location 6 | 1,721,681 | 367,252 |
| Location 10 | 13,424 | 2,253 |
+-------------+-------------+-----------+

However, I cannot get the "order by" part of the statement to work. I have tried adding a zero to the order by alias. And when I attempt to specify the field in the order by with the calculation and not the alias I get an error.

The following examples are queries without the zero and with a zero added to the alias in the order by line. I have also added the details for the table below for reference. Code:

Getting The Last Result
I have following table:
product| ordered_by | date
n1 | A | 2006-01-01
n1 | A | 2006-02-01
n1 | D | 2006-03-01
n1 | B | 2006-05-01
n2 | B | 2006-01-01
n2 | C | 2006-04-01
....

As a result I only want one set per product with the latest "ordered_by".
In this example:
n1, B and
n2, C

So far I solved it within the executing program but I was wondering if there
is an efficient query to do the same.

Result Set
I have a SELECT that returns a set of data

Code:

SELECT stats_d.link_id
FROM stats_d
LEFT JOIN links ON stats_d.link_id = links.id
WHERE links.id IS NULL AND stats_d.link_id IS NOT NULL

Now, i want to update stats_d.link_id (same value for all) in the result set.

Result Set Of Ids
I'm selecting data where the id is in a list (ie. SELECT * FROM x_table WHERE id IN (3,4,7,8,9)).
Is there a way to maintain the order of the result set based on the order of the IN list?
For example, if I constructed my IN list to be (3,2,7,6,4), my result set will be returned in that order instead of (2,3,4,6,7).

First Query Result Used For Second?
Is this possible / Practical?

Function1
I have a form that takes a users single field input and queries the database returning two values as result of what was entered, valueA and valueB. It then calls function2 passing it valueA and valueB.

Function2
takes valueA & valueB and queries the database again using these values within the query.

Im still learning mysql so the code is a real mess and not working.

Could someone give a simplified example of how this could / should be done?

Invert Result Set
SELECT `table1_id` FROM `table1` INNER JOIN `table2` ON `table1_id` = `table2_id` GROUP BY `table1_id`
This will return a unique list of table1_id's where there is relational data from table2.

What I want to do is invert this entire result set aka get a list of table1_id's that have NO current relation to table2.

I've done this years ago in mysql 3.23 but i'm currently having a metal blank...

Result From Insert
I want to insert a record and read the index value created in a single query.

It is not sufficient to insert a record with one query then read the last record with another query as a different user running the same application has a chance of inserting another record before the 2nd query (to find info on the latest record) has a chance to run.

Location Of A Row In Result
here's the problem let's say that I have the following table (msgs):

id, cid, text
1, 1, text1
2, 1, text2
3, 2, text3
4, 1, text4
5, 2, text5
6, 2, text6
7, 1, text7

now if i executed the following query:

SELECT id, text FROM msgs WHERE cid='1';

here's what I'll get

id, text
1, text1
2, text2
4, text4
7, text7

but here's what i want to get

counter, id, text
1, 1, text1
2, 2, text2
3, 4, text4
4, 7, text7

and then I want to know the location of the row that holds the id 4, which is 3 in this example.

1st: I have mysql 3.23 on my pc, and the new version of mysql is about 17-36 MB so it's hard for me to download it on a slow intenet connection, plus I don't know whats the version of mysql on my site server, so please suggest a solution that works for version 3.23.

2nd: I don't want to create temporary tables to find the results as many users at the same time could execute the same query, so the code might create thousends of tables at the same time.

3rd: I don't wanna use PHP code for this, as the result could return thousends of results, and I don't wanna go through loop for thousends of records.

please any ideas? oh btw I want to display only 5 records but I want to know what 5 records to display depending on the location the certain row that i'm looking for.

No Result For Zero COUNT
The following query

SELECT COUNT(Id) FROM References
WHERE refereeId='3'
GROUP BY targetId;

returns the desired result if there are several rows that contain the
specified refereeId, which is great.

But if there are no matching rows, nothing is returned, and I was
expecting a return value of zero.

Am I using COUNT wrong?

Large Result Set
I have a question regarding working w/ an extremely large result set - or maybe better yet how I could possibly avoid it. I have a table w/ several million records and I need to loop thru and check each record against an entry in another table and if a match is found write most of the current row's fields out to a file. My problem is when I query for the first set the result set is too large and I blow my memory.

Empty Result Set
I'm running a perl script that accesses a mysql database. I'm trying to run a simple select statement that gives me an empty result set. The wierd thing is that if I simply replace the table name with another table name - the statement returns stuff. Could the one table have some sort of issue with it?

Display More Than One Result From DB
why this:

mysql_select_db($database_MARTIN, $MARTIN);
$query_CallsAssignedCount = "SELECT StaffID, count(CallID) FROM callinfo WHERE callinfo.StaffID = '$row_Leader1[StaffID]' GROUP BY StaffID ORDER BY CallID ASC";
$CallsAssignedCount = mysql_query($query_CallsAssignedCount, $MARTIN) or die(mysql_error());
$row_CallsAssignedCount = mysql_fetch_assoc($CallsAssignedCount);
$totalRows_CallsAssignedCount = mysql_num_rows($CallsAssignedCount);

only displays one result and not all StaffIDs? Its meant to list all StaffIDs and the number of calls closed by that staff member. '$row_Leader1[StaffID]' is the coloumn that lists all the client IDs on another form. This lists all StaffIDs fine, however, this SQL command (above)only displays ONE StaffIDs result.

Sql Alternate Result Set
how to do a sql select that will alternate a result set.
I have a table called account and in account I have two different seller types 'I' and 'D'. After I run a sql query I want an 'I' record to show up and then a 'D' record and then 'I', etc....

Result From Two Tables
When I do a

SELECT * FROM table1, table2 WHERE table1.id = table2.id AND table1.id = 'value' AND table2.id = 'value'

I only get results back from one table.

Join Result Set
select table1.*, table2.* from table1 left join table2 on table1.field1=table2.field1
In php i receve a result: Array ( [field1] => 1 [field2] => something else )
I want to receive result: Array ( [table1.field1] => 1 [table1.field2] => something [table2.field1] => 1 [table2.field2] => something else )
What i need to do to get the result set i want?

Trimming Result
how may i trim the string retutned by quey?
for instance, i'd like to have three first words of a sentance.

Return Result Set In SPs
how can we have a select satement in an SP?(i want to return a result set from the SP). i want to migrate from SQL Server to MySQL and i don't know what to do with these SPs.
it can be done in MySQL client, but it gives the error:"procedure foo can't return a result set in the given context MySQL"
in the application.
can i set a cursor out put parameter and return it?(just like Oracle) or it has another solution?

and another question: is MyODBC 3.51 tested with MySQL 5.x? can we call an SP from Delphi via MyODBC? (i tried to do so (using ADO an a data provider for OLEDB) but i think somewhere in passing the call statement it had put {} around "call sp()" statement so that mysql returned an error about the syntax.)

Ordering Of The Result
I have a table which stores information on people
One of the fields that I store for each person is his nickname which is varchar(255)
I notice that I have many records in the database where the nickname has a value that starts with '!'I would like to be able to sort the data in such way that entries that start with '!' will be displayed last and all the other entries will be displayed in their correct alphabetical order.Is that possible?

Oppress A Result
i want to select information from three tables by using some COUNT() functions. The problems is that if some of COUNT return 0 the whole query goes down. Is there a way to oppress that mistake and gave a default value for the returning value.

Here is the query;
SELECT t1.c1, COUNT(t1.c1) AS var1,t2.c1,t2.c2,t2.c3,t2.c4,t2.c5,t2.c6,t3.c1 FROM t1,t2,t3 WHERE t2.c2 = PHP_VAL AND t1.c2= SAME_PHP_VAL AND t1.c5 < UNIX_TIMESTAMP() GROUP BY t1.c1;

If count of t1.c1 is 0 the whole query goes down or return the information x3. Maybe the query is imposible or my tables design is not the best. If think that if I can put a default value for a zero or 0 COUNT the problem will be fixed. Later I can easy replace it with PHP.

Result Time
is there a function in either php or sql that tells you how long a query took to process? i'm trying to find the best sql statment, and i need benchmarks.

Refine Result
is it proper way of refining results by first letter:
Code:

SELECT * FROM homes WHERE name LIKE 'a%';

Return Result Set
I was looking into returning a result set from a MYSQL 5.0 stored
procedure to php. Unfortunately I do not know what type to return nor
can I find any types that would allow me to do this. Is this possible
under the 5.0 implementation?

Return Result Set In SPs
how can we have a select satement in an SP?(i want to return a result
set from the SP). i want to migrate from SQL Server to MySQL and i
don’t know what to do with these SPs.
it can be done in MySQL client, but it gives the error:"procedure foo
can’t return a result set in the given context MySQL"
in the application.
can i set a cursor out put parameter and return it?(just like Oracle)
or it has another solution?
and another question: is MyODBC 3.51 tested with MySQL 5.x? can we
call an SP from Delphi via MyODBC? (i tried to do so (using ADO an a
data provider for OLEDB) but i think somewhere in passing the call
statement it had put {} around "call sp()" statement so that mysql
returned an error about the syntax.)

Get Result Using Join
i am retreiving from the same table with different condition

1.SELECT a.Avg_Binaryvalue as Avg1,a.Min_Binaryvalue as Min1 from MeasurementData a where Attribute_Flag ='5'

2.SELECT b.Avg_Binaryvalue as Avg2,b.Min_Binaryvalue as Min2 from MeasurementData b where Attribute_Flag ='6'

this two query gives resulsets
i need to join this two resultsets.give me sample query to join this two result sets

Sorted Result
this my query:
$sql = "SELECT * FROM aims_technicallogs WHERE aircraftid = $dv_acftid ORDER BY tcl_flyingdate, tcl_datetime DESC";

i try execute, it run without error.

but problem was the results not sorted. what the problem is?

tcl_flyingdate = date format
tcl_datetime = date and time format

i m using mysql database version 4

Ascending Order Number For Every Row In Result Set

I want to return for every row in my result set a number, which tells the order in the result set. Does there any function which enables this common feature.