0

I'm wanting to find the very last pallet placed in a given batch of locations within a warehouse.

I currently have:

SELECT

max(datreg) AS "_Reg Date",
logguser,
mha,
rack,
horcoor,
vercoor

FROM

L16T3

WHERE

l16lcode = '3'

AND

rack = @('Rack?',rack)

AND

horcoor >= @('Loc From?',horcoor)

AND

horcoor <= @('Loc To?',horcoor)

ORDER BY 1

LIMIT 1

I thought this would return just the last pallet placed in that specific location, but I'm still getting like 4 entries for one location.

I would only want the highlighted result, as that is the most recent pallet placed in 110-001-04: highlighted result

I'm sure this is super simple but im just starting out :)

MT0
  • 143,790
  • 11
  • 59
  • 117
NC1907
  • 1
  • 3
  • Make sure they're actually ordered so that the first one returned is the one that you want, then use `LIMIT 1` – Toastrackenigma Feb 10 '22 at 07:08
  • is limit 1 placed at the end of the query..? – NC1907 Feb 10 '22 at 08:29
  • Yes, you can place it at the end of the query to limit the number of results the query can return. For example, `LIMIT 1` means it can return _at most_ one result, `LIMIT 2` means at most two results, etc – Toastrackenigma Feb 10 '22 at 08:31
  • But you may need to have an `ORDER BY` clause first if your results are not already in the correct order such that the _first_ result is the one that you want. – Toastrackenigma Feb 10 '22 at 08:31
  • Because `LIMIT` essentially cuts off the ones below, e.g. if I have rows in order "A", "B", and "C", `LIMIT 1` only returns "A". If "C" was really the one I wanted (e.g. the row at the _bottom_), then I would need to do an `ORDER BY` first, so that the rows are in the order "C", "B", "A" by the time the `LIMIT` clause runs – Toastrackenigma Feb 10 '22 at 08:33
  • I get this error when trying this :/ ORA-00933: SQL command not properly ended – NC1907 Feb 10 '22 at 08:40
  • Can you [edit](https://stackoverflow.com/posts/71061002/edit) your question to show your updated command? – Toastrackenigma Feb 10 '22 at 08:42
  • Thanks for updating your question, please check out my answer below, I didn't realise you were using an Oracle db until I saw the error message --- SQL for Oracle is a little different than a lot of other popular database systems, e.g. PostgreSQL, MySQL, SQL Server, etc – Toastrackenigma Feb 10 '22 at 08:56
  • ah ok. I just noticed and it is working now. thank you very much! – NC1907 Feb 10 '22 at 10:15

1 Answers1

0

You can use a combination of ORDER BY and LIMIT to achieve what you want.

Limit

In a lot of other databases, this is called LIMIT, but I missed that you are using an Oracle database, which has a different dialect of SQL. In Oracle, the most direct equivilent of a limit is:

FETCH FIRST n ROWS ONLY

This means that your query can return at most n rows. So, for example, FETCH FIRST 1 ROWS ONLY means that it can return at most 1 row. The issue is that it takes rows from the start of the table, not the end (and despite the wording implying FETCH LAST n ROWS ONLY would be a thing, it doesn't seem to be) --- you can essentially think of it as cutting off the rows below given limit.

For example, if I have rows in order "A", "B", and "C", FETCH FIRST 1 ROWS ONLY only returns "A". If "C" was really the one I wanted (e.g. the row at the bottom), then I would need to add an ORDER BY clause to first order the results so that the one I want is at the top.

Order By

ORDER BY column dir orders your results by a specific column in a specific direction, e.g. in ascending (ASC) or descending (DESC) order. The syntax actually allows for more complex ordering (e.g. ordering by multiple columns or by a function), but for this simple case this should do what we need.

Putting it together

You want to order so that your desired row is at the top of your table, then you want to limit your results set to contain at most one row.

Adding something like this to the end of your query should work:

ORDER BY "_Reg Date" DESC
FETCH FIRST 1 ROWS ONLY
Toastrackenigma
  • 7,604
  • 4
  • 45
  • 55
  • You do not get `FETCH LAST ...` but you do get `OFFSET x ROWS FETCH NEXT y ROWS ONLY`. Additionally, this `FETCH` syntax is only available from Oracle 12; before that you will need to use `ROWNUM` or an analytic function inside a sub-query. – MT0 Feb 10 '22 at 10:21