Why do Companies make start columns' id from one?

Why do Companies make start columns' id from one ? When i tried id as 1 -100, it couldn't be find. for example. countryId : 4005 cityId: 13400

For Example; foursquare

via Chebli Mohamed

sql delete double output

I'm running a microsoft sql server. I need to do something along the lines of the following: (not working code, its just to get my point across)

DECLARE @old TABLE ( locatie NVARCHAR(256), gebruiker NVARCHAR(256), tijd DATETIME )
DECLARE @tot INT
DELETE FROM dbo.DW_D_Locaties_Inpak_Productie
    OUTPUT deleted.locatie, deleted.gebruiker, deleted.productiedatum INTO @old (locatie, gebruiker, tijd)
    OUTPUT SUM(deleted.aantal) INTO @tot
    WHERE DocumentNr='B424609'

how do I do this?

via Chebli Mohamed

using SQL to rank scores

What is the correct way in SQL to add a value to a field that is an ordinal ranking.

I say "correct" because I think I need to pull the records in order, and update the field in sequence as I loop over them - only I know this isn't efficient - I know it should be able to be done strictly in the db engine.

Here's the scenario - I have 1000 students with test score averages thru the year.

I want to rank them highest to lowest, and store their ranking int the db, such that when the record is pulled (either singularly or in a group) the 'rank' comes with the record... in other words, yes, if i pull the WHOLE set, and order by avg_score DESC, I'll get the ranking, but it wont 'stick' with the record.

So how would I do that in SQL. Specifically MySQL 5.5

STUDENTS (table)
    id (primary key)
    name
    avg_score
    rank

Thanks.

via Chebli Mohamed

Distribute two SELECTs with a given ratio

I have two tables with the following data

table "group1":

id  | sequenceNo
----+-----------
101 | 1
102 | 2
103 | 3
104 | 4
105 | 5

table "group2":

id  | sequenceNo
----+-----------
201 | 1
202 | 2
203 | 3
204 | 4
205 | 5

I have a given ration of 3:1 which should build a mix of the groups.

The result would be:

id
--
101
102
103
201
104
105

Ideally the mixing stops when one of the groups is empty.

I've implemented a solution for the problem as an OO-program. However, I am curious if there is also a simple SQL-only solution.

Many thanks,

Maik

via Chebli Mohamed

From subquery giving error in oracle

I am having this query :

SELECT T.custno,T.custlastname,AVG(T.OrderAmount) , T.OrderCount
FROM(
      SELECT  A.custno,A.custlastname,count(b.ordno) as OrderCount,    sum(c.qty*d.prodprice) AS  OrderAmount
  FROM customer A 
      JOIN ordertbl B ON A.custno=b.custno
      JOIN ordline C ON b.ordno=c.ordno
      JOIN  product D ON c.prodno=d.prodno
  WHERE A.custstate='CO'
  GROUP BY A.custno,A.custlastname, b.ordno) AS T
GROUP BY T.custno,T.custlastname;

I get this error :

ORA-00933: SQL command not properly ended

When i execute inner subquery explicitly, it runs fine. Please let me know the reason.

One can try at http://ift.tt/1fbp56t

via Chebli Mohamed

MySQL: How can I check whether Users are listed or not

I was wondering how I can check whether users are already in the database or not.

In PHP I have an array with some UserIDs. i.e. userIDs[0] = 1234; userIDs[1] = 2345;

Now I wanted to build a query to make just one sql call if possible to get following result:

############################
#    UserID    #   Exists  #
############################
#     1234     #     0     #
#     2345     #     1     #
############################

Is there a sql solution or do I have to check each ID with a seperate call? Thank you for your help!

via Chebli Mohamed

How to use 'Insert' in a nativeSQL query in Hibernate outside the mapped class ?

In Hibernate, you can use the 'SELECT' queries in native SQL like this :

Query query = session.createSQLQuery("SELECT ... FROM ...");

But I would want to use an 'INSERT' query.

So, I looked at the documentation, and it seems you must go directly to the mapped class and write the code inside it.

But I would want to use it as I do for a 'SELECT' query (outside the mapped class) since it looks much more pratical.

Indeed, why would the treatment be different between 'SELECT' and 'INSERT' for a hibernate native SQL query ?

via Chebli Mohamed