Ascending doesn't work

[pinkod02]

SELECT * FROM products ORDER BY product_id, name, price;
Why does it give an error? i need to sort ascending alphabetically

[Thallius]

It could help if you tell us what error you get. The query itself is bad style, as you should not use select *, but it is not producing an error

[m_hutley]

The error will tell you precisely what went wrong;

name is a reserved word and may be throwing some form of error (but shouldnt be in this context). Do these fields actually exist in the table?

[pinkod02]

I do this on the site, it just writes an error, from the Task they ask to display the entire table in ascending alphabetical order, but in fact it was necessary to do this,
SELECT product_id,
name,
price
FROM products
ORDER BY name asc;
such a decision is not clear to me, here I only display the name, sort it, and write in the task,
Output all records from the products table, sorting them by product name in alphabetical order, i.e. Ascending. Use the ORDER BY clause to sort.

Fields in the resulting table: product_id, name, price

[Thallius]

@m_hutley

name is a reserved word

I don’t think so….

[m_hutley]

sorry, i should have said that better. It’s a keyword, not a reserved word.

[Thallius]

Not even that…

[m_hutley]

https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-8-0-detailed-N

It’s definitely a keyword.

[Thallius]

@m_hutley

tps://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-8-0-detailed-N

It’s definitely a keyword

Ok, from that list nearly all words are keywords

[Gandalf]

What does the error say? You have put this in the PHP category. Maybe your error is elsewhere in the code.

You seem to expect us to answer your queries without providing enough information.

[SamuelCalifornia]

@pinkod02

SELECT product_id,
name,
price
FROM products
ORDER BY name asc;

Are you saying that works or do you still need help?

I used SQLite3 Fiddle to test the following.

DROP TABLE products;
-- END TRANSACTION;
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "products" (
	"product_id"	INTEGER NOT NULL UNIQUE,
	"name"	TEXT,
	"price"	NUMERIC,
	"description"	TEXT,
	PRIMARY KEY("product_id")
);
INSERT INTO "products" VALUES (1,'dishes',25,NULL);
INSERT INTO "products" VALUES (2,'silverware',40,NULL);
INSERT INTO "products" VALUES (3,'glasses',35,NULL);
COMMIT;
SELECT product_id,
name,
price
FROM products
ORDER BY name asc;

That works, except we need to comment out or uncomment lines as needed.

[spaceshiptrooper]

This is precisely it. I’ve learned through the years that certain keywords don’t necessarily stop you from using them. name is one of them. Otherwise people can’t have a name column to store user’s full name. If that’s the case, everyone would have to start using other things like full_name, first_name, etc. This also applies to using the word “password”. The only one I’ve encountered that would definitely throw an error is the use of the word key. I’ve attempted to use this word once to store my “activation keys” and I do actually get the “this word is a reserved keyword” error.

Back on topic, I don’t think that’s how you can do GROUP. Isn’t it supposed to be something like GROUP BY id DESC, name ASC (just an example)?

[Drummin]

@pinkod02

SELECT * FROM products ORDER BY product_id, name, price;

You are sorting by your primary KEY first so the results will always be in that order before ordering by the second then third field listed. For example If you were ordering by lastname, firstname, age, records with the same last name would be grouped together and then within this grouping they would be ordered by the first name, and then by age.

In your case you could ORDER BY name and then price and they would be ordered as expected.

You can always use backticks around fields or table names, which might be giving you trouble.

SELECT 
 `product_id`
,`name`
,`price` 
FROM `products`
ORDER BY `name`, `price` asc

[rpkamp]

@spaceshiptrooper

Isn’t it supposed to be something like GROUP BY id DESC, name ASC

Nope, grouping doesn’t have ordering. E.g., the SUM of all columns will be the same regardless of which order you’re walking through the rows. Same for all other aggregate methods, the order just doesn’t matter. So it best to just leave the order to the database engine to find rows in the order it can find them the fastest.

[spaceshiptrooper]

Yeah, I realized I typed GROUP instead of ORDER. lol, that’s what happens when I’m typing on a phone. Meant to actually say ORDER BY. Couldn’t edit my response until it was too late.

[pinkod02]

I imagine such work, if I thus indicate to select all, and indicate the columns in order, then it sorts each column either in ascending or descending order, that is, the line can no longer correspond to real data.

[SamA74]

As @Drummin explained, the columns are ordered in the order you write in after the ORDER BY part.
So if product_id comes first, it will be ordered by product_id.
If you want to order by a different column, put that column first.

[Drummin]

Yes, if you want records ordered by their product_id ASC or DESC you can do that but because it is a unique field there would never be a case where you have more than one record with this ID so the second ORDER BY field name would never be used…

IF you DID have more than one record with this product_id, then out of those it would ORDER BY name, then it would only be when you have more than one record with this name that the price might be factored into the ORDER BY condition.

This why we’ve suggested just ordering by name and price.