Have 2 table 1st {id_city,name } 2nd table {id_ctz,name,sname,age,id_city} need selected full information (name(city),name,sname,age) people with same name ascending by age? Have tried: select …
Tag: mysql
MySQL update statement to store ranking positions
I’m trying to get my head around a query and I just can’t figure it out. I would appreciate if someone give me a pointer. As a simple example of what I’m trying to achieve, I have these records in the …
Can I use non-aggregate columns with group by?
You cannot (should not) put non-aggregates in the SELECT line of a GROUP BY query. I would however like access the one of the non-aggregates associated with the max. In plain english, I want a table …
How to select a row for certain (or give preference in the selection) in mysql?
Need your help guys in forming a query. Example. Company – Car Rental Table – Cars Now, how do I select only one entry from this table which satisfies the below conditions? If Mercedes is available in Showroom, then fetch only that row. (i.e. row 1 in above example) But If none of the Mercedes are available in the showroom,
Indexes and multi column primary keys
In a MySQL database I have a table with the following primary key In my application I will also frequently be selecting on item by itself and less frequently on only invoice. I’m assuming I would benefit from indexes on these columns. MySQL does not complain when I define the following: But I don’t see any evidence (using DESCRIBE —
Group by date range on weeks/months interval
I’m using MySQL and I have the following table: I want to be able to generate reports like this, where periods are done in the last 4 weeks: or in the last 3 months: Any ideas how to make select queries that can generate the equivalent date range and clicks count? Answer
Export unicode data from mysql
I have some data in one of my mysql table stored as utf8. The data is some japanese text. I need to export it to excel. Could you tell how to do it? Exporting by SELECT … INTO OUTFILE returns some plain text file. I’m not sure how to read it back in excel so that japanese character would show
Join one row to multiple rows in another table
I have a table to entities (lets call them people) and properties (one person can have an arbitrary number of properties). Ex: People Properties I would like to write an efficient select that would select people based on age and return all or some of their properties. It’s also acceptable to return one of the properties and total property count.
Avoiding Nested Queries
How Important is it to avoid nested queries. I have always learnt to avoid them like a plague. But they are the most natural thing to me. When I am designing a query, the first thing I write is a nested query. Then I convert it to joins, which sometimes takes a lot of time to get right. And rarely
MySQL ON DUPLICATE KEY UPDATE for multiple rows insert in single query
I have a SQL query where I want to insert multiple rows in single query. so I used something like: The problem is when I execute this query, I want to check whether a UNIQUE key (which is not the PRIMARY KEY), e.g. ‘name’ above, should be checked and if such a ‘name’ already exists, the corresponding whole row should