CODEWITHSUNDEEP
mysqlsql-order-by
RGS
RGS

Reputation: 4253

show the newest images first - table with no id or date?

I have this table:

CREATE TABLE IF NOT EXISTS `profile_picture` (
  `user` int(11) UNSIGNED NOT NULL,
  `photo` varchar(400) NOT NULL,
  PRIMARY KEY (`user`),
  FOREIGN KEY (`user`) REFERENCES users (`id`)
);

This select shows me the oldest posts first, I'd like to show the newest. but I havent an id or date row. can I do this?

SELECT p.user, p.photo, c.user from profile_picture p
join users c on c.id=p.user
limit 400

Upvotes: 0

Views: 19

Answers (2)

fifonik
fifonik

Reputation: 1606

It is absolutely correct that was already explained (that if you do not have ORDER BY clause that the order is not defined and it is up to SQL server to return rows).

However, when there is not GROUP BY/ORDER BY clause, MySQL usually returns values in the order they were added. So you can try to hack a little:

SELECT * FROM (
    SELECT (@i := @i + 1) AS i, table.* FROM table
) AS s
ORDER BY i DESC;

WARNING: I do not recommend you to use it. It may or may not work at all in your system. It may stop working one day

I would never use it myself. I'd add column 'inserted TIMESTAMP DEFAULT CURRENT_TIMESTAMP' instead.

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562270

One of the principles of relational databases is that the rows in a table have no implicit order. They are a set in the mathematical sense.

So if you want to query them in some specific order, you can only do that by using values stored with each row, and referencing these in an ORDER BY clause.

If you have no column int his table you can use to order the rows, then you can't order.

As an analogy, if you have a pile of pages for your manuscript, with no page numbers, and you drop the pile on the floor, good luck putting them back in order.

Upvotes: 2

Related Questions