This query will give you a list of email addresses and how many times they’re used, with the most used addresses first. SELECT email, count(*) AS c FROM TABLE GROUP BY email HAVING c > 1 ORDER BY c DESC If you want the full rows: select * from table where email in ( select … Read more
Create a table with the set you want to export and then use the command line utility pg_dump to export to a file: create table export_table as select id, name, city from nyummy.cimory where city = ‘tokyo’ $ pg_dump –table=export_table –data-only –column-inserts my_database > data.sql –column-inserts will dump as insert commands with column names. –data-only … Read more
The relational algebra allows interchangeability of the predicates in the WHERE clause and the INNER JOIN, so even INNER JOIN queries with WHERE clauses can have the predicates rearrranged by the optimizer so that they may already be excluded during the JOIN process. I recommend you write the queries in the most readable way possible. … Read more
If you are like me and have certain restrictions in a production environment, you may wish to use a table variable instead of temp table, and an ad-hoc query rather than a create procedure. Of course depending on your sql server instance, it must support table variables. I also added a USE statement to narrow … Read more
An illustration from W3schools:
If you are using SQL 2005 you can do something like this… SELECT rs.Field1,rs.Field2 FROM ( SELECT Field1,Field2, Rank() over (Partition BY Section ORDER BY RankCriteria DESC ) AS Rank FROM table ) rs WHERE Rank <= 10 If your RankCriteria has ties then you may return more than 10 rows and Matt’s solution may … Read more
DATE_FORMAT(FROM_UNIXTIME(`user.registration`), ‘%e %b %Y’) AS ‘date_formatted’
Bottom Line Use either COUNT(field) or COUNT(*), and stick with it consistently, and if your database allows COUNT(tableHere) or COUNT(tableHere.*), use that. In short, don’t use COUNT(1) for anything. It’s a one-trick pony, which rarely does what you want, and in those rare cases is equivalent to count(*) Use count(*) for counting Use * for … Read more
Simple answer: Database vendor independent solution = use the standard = COUNT(*) There are approximate SQL Server solutions but don’t use COUNT(*) = out of scope Notes: COUNT(1) = COUNT(*) = COUNT(PrimaryKey) just in case Edit: SQL Server example (1.4 billion rows, 12 columns) SELECT COUNT(*) FROM MyBigtable WITH (NOLOCK) — NOLOCK here is for … Read more
It occurred because you tried to create a foreign key from tblDomare.PersNR to tblBana.BanNR but/and the values in tblDomare.PersNR didn’t match with any of the values in tblBana.BanNR. You cannot create a relation which violates referential integrity.