I have an SQL statement that grabs the grades of different activity types (Homework, Quiz, etc), and if there’s a drop lowest for that type, it drops, else, it remains. The errors are below as well as the SQL Code. Here are the errors I’m getting: Answer Look into analytical functions. (SO questio…
Tag: sql
How can SELECT COUNT(*) different from count of all records in the table?
So I have a table: SELECT COUNT(*) returns a different value than SELECT DISTINCT COUNT(*). How can this be possible? I also tried which returned the same count as the distinct query. I can’t see how with a primary key and all fields being NOT NULL, there can be a different total count than the count of…
Need to list all triggers in SQL Server database with table name and table’s schema
I need to list all triggers in SQL Server database with table name and table’s schema. I’m almost there with this: I just need to get the table’s schema also. Answer Here’s one way: EDIT: Commented out join to sysusers for query to work on AdventureWorks2008. EDIT 2: For SQL 2000
SQL: Advantages of an ENUM vs. a one-to-many relationship?
I very rarely see ENUM datatypes used in the wild; a developer almost always just uses a secondary table that looks like this: But the same thing can also be shown using a user-defined type / ENUM: (Example shown using PostgreSQL, but other RDBMS’s have similar syntax) The biggest disadvantage I see to …
Insert Fail because of wrong non-numeric character
hy table: create table Players (PlayerNo number (4) not null, Name varchar2(15), date_of_birth date,leagno varchar(4)); wrong insert: insert into PLAYERS (PlayerNo,Name,date_of_birth,leagno) …
SQL Server: Why do use SMO?
I have been working with SQL Server for a couple of years. I have heard about SMO but I don’t know anything about it. What are the benefits of using it? Should I learn and start using SMO in my SQL …
Why am I getting “Enter Parameter Value” when running my MS Access query?
I’m about selecting all employees who come New York but whenever I run the query, I always get a “Enter Parameter Value” box. How can I fix this? Answer This is because Access does not allow you to use field aliases in the query – it does not recognize [City Name] as a valid field name. Aliases ar…
Substring in select statement in JPQL
I’m trying to select a substring of column, i.e. select substring(description, 1, 200) from category where id=1 Is it possible to have a substring function within a select statement in JPQL/JPA 2? If yes, how? If no, are there any alternatives? Thanks. Answer There is a scalar expression for this: SUBST…
How to drop multiple tables in PostgreSQL using a wildcard
When working with partitions, there is often a need to delete all partitions at once. However Does not work. (The wildcard is not respected). Is there an elegant (read: easy to remember) way to drop multiple tables in one command with a wildcard? Answer Use a comma separated list: If you realy need a footgun,…
SQL Server SELECT LAST N Rows
This is a known question but the best solution I’ve found is something like: SELECT TOP N * FROM MyTable ORDER BY Id DESC I’ve a table with lots of rows. It is not a posibility to use that query …