Skip to content

Count of line item in MySQL

I have a complex query joined to multiple tables to show a “transaction history”, I want a quick and easy way to count the occurrences of order_id to make it as the line_number. I had it done with code but for performance reason I want to have it done with MySQL. Here is an example of what I have:…

Ranking subcategories while keeping order of ID

I have the following table: I would like to rank the subcategory (cat2). Desired outcome: I use DENSE_RANK with PARTITION BY to get the following result: Statement: As you can see, the only thing I’m missing is the order of the result. Currently, the rank is based on the alphabetic order of cat2. Howeve…

Try to define a left join only with where condition

I try to find a particular SQL statement to replace an old SQL query. To summarize, I try to make a left join only with where conditions. Here is my test environment: I fill the tables with some data: The usual way for a left join is this: This query returns 1266 rows. But in the old application, which I

Unexpected token “” was found following “”

Im a newbie to DB2. Im trying to convert this sp from Microsoft SQL server to DB2 Here is the sp from Microsoft SQL: and this is the sp after i converted to DB2 Then i got this error: An unexpected token “” was found following “”. Expected tokens may include: “”.. SQLCODE=-…

SQL:pivot: convert each row into many rows depends on a condition

I have been trying to solve this but could not figure it out. so this is the table and more columns could be added: So if the value is one then I will display it in a different row but with the value being the column header itself My boss suggested using pivot to solve this but I am open