Chris

I am experiencing a brain fart.

( For history. )

The reason?  I used distinct.  *facepalm*

Originally posted on Dreamwidth.  Number of comments so far:

Check me on this, if you would/can.

This is the SQL statement:

select u.lastname, u.firstname, u.company, a.datepaid
from users u
left join assignments a on a.userid = u.id
where a.datepaid >= '2010-01-01'
order by u.lastname, u.firstname, u.company, a.datepaid

I am trying to... paginate, really.  10 records per page.

What I would do in MySQL is append this line to the very end:

limit 10, 10

However, I don't know how to do that in SQL Server.  I know how to include top 10, but not how to do the offset.  The closest I have:

select top 10 u.lastname, u.firstname, u.company, a.datepaid
from users u
left join assignments a on a.userid = u.id
where a.datepaid >= '2010-01-01'
and a.id not in
(
  select top 10 a.id
  from users u
  left join assignments a on a.userid = u.id
  where a.datepaid >= '2010-01-01'
  order by u.lastname, u.firstname, u.company, a.datepaid
)
order by u.lastname, u.firstname, u.company, a.datepaid

Then for each page, add 10 to the top # in the parenthesised code.

That just looks clunky as virtue, though.

Is there a better way of doing this?

Originally posted on Dreamwidth.

Stupid question:

There is a table with this structure:

* id
* title
* parentid

So things like this:

1 | Monkeys  | null
2 | Capuchim | 1
3 | Kitties  | null
4 | Puppies  | null
5 | Labrador | 4
6 | Terrier  | 4

I want a way, using T-SQL, to pull up something like this:

5 | Labrador | Puppies
6 | Terrier  | Puppies

I probably already know how and I am blanking.  Halp?

Firefox 3 is ♥.  Today's adoration is for how it remembers what phrase I typed to get which URL.  For example, when I type 'rea', it pulls up the Google Reader URL, and when I type 'read', it brings up my reading page here.  *claps hands*

I had to train it a lot to get that example, though, as I'd spent ages typing in 'rea' and 'read' to get Google Reader.  Fifty million times later, I finally trained it to distinguish between the two URLs.

You know what's fun?  By which I mean to say, fun for me?  Installing SQLite Manager, and watching the phrase/URL counts change.

* Install that addon.
* Go to Tools > SQLite Manager.
* Go to Database > Connect Database.  This is where I go to; yours may be different:
  C:\Documents and Settings\[user name]\Application Data\Mozilla\Firefox\Profiles\[profile folder]\
* In there, open places.sqlite.
* Click on the Execute SQL tab, and type this query in the box:

select h.input, h.use_count, p.url
from moz_inputhistory h
left join moz_places p on p.id = h.place_id

* Filter on whatever you like to get the results you need.  Using my above example, I'd want to see everything where I type in at least 'rea' to get it (covering both 'rea' and 'read'):

select h.input, h.use_count, p.url
from moz_inputhistory h
left join moz_places p on p.id = h.place_id
where h.input like 'rea%'

--

I still don't know why the numbers aren't integers, and why there are sometimes multiple entries for the same URL.  But hey, tinkering!  I like tinkering.  And I love SQL, so, bonus.

I am so awesome, oh my god.

I'm slowly making changes to both our website's backend database and the SQL statements that the site uses.  For lo, I'm tired of it always being shitty, but the chances of me having a week to make sweeping changes are exceedingly low.

Today, I changed a query in the PHP:

From: INSERT INTO table VALUES ('name', 'email', null, null, 0)
To: INSERT INTO table (colName, colEmail, colUses) VALUES ('name', 'email', 0)

Why?

- Knowing where values are going is good.  Lord knows we don't comment anything around here.
- Makes it way easier to add or remove a column.
- Why waste time always inserting null values?
- It's good SQL practices, goddammit.

If this gets changed, then the part of me that's always annoyed by our site will be less annoyed.  And that will be Good.


In case you ever wondered what pleases me, this is an example.

So, this is what I discovered yesterday:

- I can design SSIS packages.  There's a little GUI tool for this, can't recall the name.  (DTSWizard?)
- I can open SSIS packages in SQL Server Management Studio.  I can edit the queries, add or delete things, and so forth.
- I can run individual SSIS packages.  'dtexecui.exe', I think.  (I'm not at work.)

But when I create an SSIS package and save it to SQL Server (as versus to a file), I can't edit it.  Because we have Workgroup edition, not Standard or Enterprise edition.  Even though I can, using dtutil.exe, move or copy the package from SQL Server to a file.  Then I can edit it like any other package.  Then I use the util to move or copy it back into SQL Server.

...

I'm missing a huge something here.  Why'd they take something away when (a) they left everything else behind and (b) I wrote code in like 15 minutes that'll do what it did anyways?  I mean, I know there must be other differences than just that one, but what the fuck?

(In fact, packages saved to SQL Server are stored in msdb.sysdtspackages90, with the details stored as XML in a column of type 'image'.  Image??  Maybe I'll post said code.)

One of these days, I gotta take a class.  Learning this stuff willy-nilly is like sitting down in the middle of a murder mystery.  "Wait, who's that guy?"

Okay, somebody distract me, I'm not allowed to find a way to write SSIS packages in XML format and write them directly to the msdb table.

I swear to FUCK.  If I never EVER have to deal with "The step failed" error messages again, it'll be TOO FUCKING SOON.

WHY can't I open an SSIS package in SQL Server Management Studio?  Whywhywhy?  It tells me that the specified service does not exist as an installed service.  Guess what's hard to search for!  That's right, 'services', since 'services' is part of the damned name.

Too early o'clock for this bullshit.

What the fucking fuck is 'Latin1_General_CI_AI'/'SQL_Latin1_General_CP1_CI_AS' even the fuck all about?